RDA Limitations

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

  • Case sensitivity. SQL Server Compact Edition databases are not case-sensitive.
  • 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 Edition 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 Edition 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.

SQL Server Compact Edition can track changes to the resulting SQL Server Compact Edition table. By specifying the appropriate tracking option, SQL Server Compact Edition tracks all inserts, updates, and deletions made to the local SQL Server Compact Edition 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 Edition 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 Edition 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 Edition table and repull the changed SQL Server table.

Community Additions