BUG: A transfer of data from a SQL Server CE or SQL Server 2005 Mobile Edition database to a remote SQL Server database fails when you use the Push method in SQL Server CE Remote Data Access (330741)

SYMPTOMS

When you use the Push method in Microsoft SQL Server 2000 Windows CE Edition 2.0 (SQL Server CE) or SQL Server 2005 Mobile Edition Remote Data Access (RDA) to transfer data from a SQL Server CE or SQL Server 2005 Mobile Edition database table to a remote SQL Server database table, the data transfer may fail. This problem may occur if all the following conditions are true:

The destination table in the remote SQL Server database contains one or more columns that are defined as NOT NULL.
The SQL Server CE database table contains rows with an empty string or with space values for columns that correspond to the NOT NULL columns on the destination table.

If a row with an empty string or with space values is inserted in the destination table, you may notice NULL values in the nullable columns.

You may not notice this behavior when you use replication to transfer data between a SQL Server CE or SQL Server 2005 Mobile Edition database and a SQL Server database.

Note This problem does not occur in Microsoft SQL Server 2000 Windows CE version 1.1.

MORE INFORMATION

Steps to reproduce the behavior

Run the following Transact-SQL statement on the remote SQL Server database:
```
CREATE TABLE TestNull (c1 INT PRIMARY KEY, c2 NVARCHAR(30)  NOT NULL)
```
Using an application that uses SQL Server CE RDA, pull the TestNull table into the SQL Server CE database on your handheld device.

Run the following Transact-SQL statements on the SQL Server CE database to insert test rows:

INSERT INTO TestNull (c1, c2) VALUES (1, ' ') 
INSERT INTO TestNull (c1, c2) VALUES (2, '  ')
INSERT INTO TestNull (c1, c2) VALUES (3, '')
INSERT INTO TestNull (c1, c2) VALUES (4, NULL)

Using SQL Server CE RDA, push the TestNull table back to the remote SQL Server database.

Note You may notice that the insert of all the rows fails. The insert fails because the c2 column in the TestNull table on the remote SQL Server database does not accept NULL values.

Workaround example

Re-create the TestNull table with the c2 column as nullable, and then perform the data transfer by using SQL Server RDA. To do so, follow these steps:

Run the following Transact-SQL statement on the remote SQL Server test database:

DROP TABLE TestNull
GO
CREATE TABLE TestNull (c1 INT PRIMARY KEY, c2 NVARCHAR(30)  NULL)

Using an application that uses SQL Server CE RDA, pull the TestNull table into the SQL Server CE database on your handheld device.

Run the following Transact-SQL statements on the SQL Server CE database to insert test values:

INSERT INTO TestNull (c1, c2) VALUES (1, ' ') 
INSERT INTO TestNull (c1, c2) VALUES (2, '  ')
INSERT INTO TestNull (c1, c2) VALUES (3, '')
INSERT INTO TestNull (c1, c2) VALUES (4, NULL)

Using SQL Server CE RDA, push the TestNull table back to the remote SQL Server database.

Note You may notice that all the rows in the TestNull table are inserted with NULL values for the c2 column on the remote SQL Server database.