Remote data access is often initiated with by pulling data from a Microsoft SQL Server to a SQL Server Compact 3.5 client database. When data is pulled from a server to a client, the pull is initiated by SQL Server Compact 3.5. SQL Server Compact 3.5 extracts data from a SQL Server database and stores the data in a table in a SQL Server Compact 3.5 database.

To pull data from the server, you can programmatically configure RDA in your application. When setting up and initiating a pull operation using either technique, you will specify the following:

  • The database from which you want to pull data

  • The Web server authentication needed to connect to the Web server.

  • The SQL Server authentication that is used

  • The tables that will be copied and propagated to the client

  • Pull options including tracking and an error table.

Tracking

An application can track the changes made on a SQL Server Compact 3.5 table by setting the appropriate tracking options. SQL Server Compact 3.5 tracks all inserts, updates, and deletes.

Applications can then call the Push method to propagate the changes back to the original SQL Server table.

Remote data access (RDA) tracked pull and push operations 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 might cause changes made by other users of the SQL Server database to be lost.

You should 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 which tracks each technician who has a unique list of service calls.

RDA_TRACKOPTION specifies whether SQL Server Compact 3.5 should track changes to the pulled table. Specify TRACKINGON or TRACKINGON_INDEXES if you want to update the pulled table on the Windows Mobile-based device and then push changed records back to the original SQL Server table. When TRACKINGON is specified, PRIMARY KEY constraints are created on the pulled table. When TRACKINGON_INDEXES is specified, PRIMARY KEY constraints and additional indexes which exist on the server table are created on the pulled table. In both cases, SQL Server Compact 3.5 keeps track of every record that is inserted, updated, or deleted in the local table.

An index is created only if the columns that makes up the index is pulled. No referential integrity constraints are created on the pulled table, since the related tables might or might not exist in the SQL Server Compact 3.5 database. These must be added back by code within the application.

When the application calls the Push method, SQL Server Compact 3.5 uses the change tracking information to locate the inserted, updated, and deleted records in the local SQL Server Compact 3.5 table and propagate these changes back to SQL Server table.

The following restrictions apply when RDA_TRACKOPTION is set to TRACKINGON or TRACKINGON_INDEXES:

An error occurs if the SELECT statement returns a nonupdatable recordset.

A primary key must be defined on the updatable recordset returned by the SELECT statement. The SELECT statement can reference a view or stored procedure, but the view or stored procedure must reference only one table and must be updatable.

When TRACKINGON_INDEXES is specified, indexes that exist on the SQL Server table and are relevant to the columns specified in SQLSelectString are created against the SQL Server Compact 3.5 local table specified in LocalTableName.

Tasks

How to: Pull Data by Using the RDA Object (Programmatically)