• Article

Remote data access (RDA) in Microsoft SQL Server Compact 3.5 has limitations in the following areas:

  • Case sensitivity. Starting with the SQL Server Compact 3.5 Service Pack 1 release, SQL Server Compact supports setting the case-sensitive collations at the database level. Object names, such as table names, language keywords, functions, and views, are not treated as case sensitive in a SQL Server Compact database, even if the collation is case sensitive.

    Objects are treated differently in a SQL Server Compact database than they are treated in a SQL Server database. For example, two tables, MYTABLE and mytable, are treated as different objects in a case-sensitive SQL Server database. The same table names cause a naming conflict in a SQL Server Compact database. Conversely, two values, MYVALUE and myvalue, no longer cause a primary key violation in SQL Server Compact because they are treated as different values. They also do not cause a violation in a case-sensitive SQL Server database. 

  • ROWGUID. When you use RDA to pull data from a table in SQL Server that also participates in replication on the server, you must exclude the system column with the ROWGUIDCOL attribute. By default, the name of this column is RowGuid.

    For example, for the SQLSelectString parameter in the Pull method, use SELECT CompanyName, ContactName FROM Customers instead of SELECT * FROM Customers.

  • Triggers. SQL Server Compact 3.5 does not support triggers. However, triggers can reside on the SQL Server table that RDA pulls data from. For tracked tables, the SQL Server triggers can be executed when changes are pushed back to SQL Server. You might have to specify SET NOCOUNT ON in the trigger logic. This indicates not to return the number of rows affected, because a response of "no rows affected" causes an error for the RDA Push method.

  • Tables and columns. For tracked tables, the number of columns available are 1017 (1024 minus seven system columns). These seven system columns are protected and used for tracked RDA tables.

  • Computed columns. The Pull method fails if the recordset contains computed columns.

  • IDENTITY columns. If you use IDENTITY columns to generate uniqueness, RDA does not dynamically manage IDENTITY columns. The default starting values for an IDENTITY column on the device is always seed=1, increment=1. SQL Server Compact 3.5 lets you modify the identity SEED and identity INCREMENT values in the local database table by using the ALTER TABLE statement. This lets you manage identity range manually for each device. You will have to determine the largest IDENTITY value and adjust the seed value before trying any inserts into the table. For example, if the largest IDENTITY value is determined to be 99, use ALTER TABLE Orders ALTER COLUMN OrderId IDENTITY (100,1) to reset the next IDENTITY value to 100.

Schema Limitations

SQL Server Compact 3.5 can track changes to the resulting SQL Server Compact 3.5 table. By specifying the appropriate tracking option, SQL Server Compact 3.5 tracks all inserts, updates, and deletions made to the local SQL Server Compact 3.5 table. The application can then call the Push method to propagate these changes back to the original SQL Server table.

RDA-tracked Pull and Push methods use optimistic concurrency control. SQL Server does not keep pulled records locked. When the application calls Push, the changes made to the local SQL Server Compact 3.5 database are unconditionally applied to the SQL Server database. This could cause changes made by other users of the SQL Server database to be lost.

Use tracked Pull and Push methods when the records you are updating are logically partitioned and conflicts are unlikely. For example, tracked Pull and Push methods might be used in a field service application that tracks each technician who has a unique list of service calls.

You can make the following schema changes to a SQL Server Compact 3.5 table created with a tracked Pull:

  • Change the seed or increment of the identity column.

  • Add or drop DEFAULT constraints.

  • Add or drop foreign keys.

  • Add or drop indexes.

  • Drop the table.

You cannot make any one of the following schema changes to a SQL Server table while a tracked Pull is still processing:

  • Add, drop, or rename columns.

  • Rename the table.

  • Drop a primary key.

  • Change a data type definition.

To make these SQL Server table schema changes, you must drop the SQL Server Compact 3.5 table and repull the changed SQL Server table.