When you use the Push method in Microsoft SQL Server 2000 Windows CE Edition 2.0 (SQL Server CE) or SQL Server 2005 Compact Edition Remote Data Access (RDA) to transfer data from a SQL Server CE or SQL Server 2005 Compact 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 Compact Edition database and a SQL Server database.

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

The Push method in SQL Server CE RDA or in SQL Server 2005 Compact Edition RDA treats empty string or space values as NULL values.

To work around the problem, change the column definition of the columns that are defined as NOT NULL on the destination table in the remote SQL Server database to NULL.

Note Before you re-define a column of the destination table as NULL, make sure that this change does not affect the integrity or the consistency of your database.

Steps to reproduce the behavior

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

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

2. Using an application that uses SQL Server CE RDA, pull the TestNull table into the SQL Server CE database on your handheld device.
3. 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)

4. 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:

1. 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)

2. Using an application that uses SQL Server CE RDA, pull the TestNull table into the SQL Server CE database on your handheld device.
3. 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)

4. 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.

For more information about the Push method in SQL Server CE RDA, visit the following Microsoft Web site: