RDA Conflict Detection and Reporting

Remote data access (RDA) in Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) provides a limited conflict reporting mechanism for rows that cannot be updated on the computer that is running SQL Server during a push operation.

Important

Conflicting rows in RDA are strictly defined as insert, update, or delete operations that fail because of an error when pushed from SQL Server Compact Edition to the SQL Server table. Changes to data by different users are not considered conflicts if they do not cause errors.

Although RDA does not provide a specific resolver the way that replication does, SQL Server Compact Edition provides an error table that captures all conflicting rows. You can specify the error table as part of the Pull method. Any errors that occur during the push are recorded in this error table. By using the error table, you can develop applications to manage conflict detection and reporting.

Changes made on the SQL Server Compact Edition database that are pushed to the server are applied in the order in which they are received. The SQL Server table is updated to reflect the changes made by the last user.

In RDA, a conflict exists when a row cannot be pushed from SQL Server Compact Edition to SQL Server. RDA supports only row-level tracking. Therefore, some rows succeed and others fail depending on which options are selected in a Push method. To track conflicts in RDA, specify TRACKINGON or TRACKINGON_INDEXES in the Pull method.

You can avoid conflicts and errors when you use RDA-tracked tables by correctly filtering tables and using a stable network connection when propagating data.

How RDA Conflicts Occur

Changes to a row cannot be applied at the server for the following reasons:

• RDA tracks insert, update, and delete operations specifically for each row that has been changed in the tracked table on the device. Therefore, if a row is inserted at the client with the same primary key value as a row that has also been inserted at the server in the same table, the push from the client will fail with an error because the insert has already occurred.
• If the data has not been correctly partitioned for each user, one user can delete a row when another user is trying to update the same row.
• Rows can also fail to be pushed to the server, causing errors if a previous push was interrupted. For example, a user starts to push their data to the server, which contains inserts, and during the push, network connectivity is lost. The client displays a message that the push failed because of network connectivity loss. However, the changes actually did apply at the server. When the client regains network connectivity and the user tries a second push of the same data, some of the rows fail to apply because those rows were inserted during the previous push. In this case, the application should ignore all errors in the error table that were failures because of a duplicate primary key based on the second push.

Error Tables

RDA tracks data conflicts, rows that could not be applied to the server because of an error, by returning and storing the errors in an error table in the SQL Server Compact Edition database together with the row that failed. You will define this table in the Pull method. Later, if any errors occur during a push operation, they will be stored in the error table.

When you use the Push method, if a row cannot be applied to the server because of an error, such as a duplicate primary key, you will reference the error table name that you originally defined in the Pull method to resolve the row. The ErrorTableName property of the Pull method specifies the name of the table in which push errors should be stored. The error table is created immediately but at first contains no rows. ErrorTableName can be specified only when TRACKINGON or TRACKINGON_INDEXES is specified in the Pull method.

If an error occurs because a row cannot be applied during the Push method, SQL Server Compact Edition inserts a record in the table for each error that occurs. Together with all the columns from the base table, three additional columns are added to identify why and when the error occurred. The s_ErrorDate column specifies the date and time when the error occurred. The s_OLEDBErrorNumber column specifies the HResult of the error that occurred when you apply the row to the server. The s_OLEDBErrorString column is a string description of the error. When the Push method finishes and errors occurred when it tries to apply rows to the server, a warning (SSCE_WRN_RDAERRORROWSRETURNED, value 28800) will be reported to the application and the application can examine the error table to determine the cause of the errors.

Maintaining Error Tables

Error tables are automatically deleted if the associated RDA-tracked table is dropped, even if rows still exist in the error table. The developer must resolve rows that are considered conflicts because those rows cannot be applied at the server.

Refreshing the data on the device might be required to correctly resolve the error that occurred when originally pushing data to the server. We recommend that you cache the data in the error table so that it is not lost when you drop the tracked table. Alternatively, you could pull the refreshed data to a table that has a different name than the original tracked table.

Resolving Errors After a Data Push

The error, stored in the error table together with the row that failed, describes why the row failed to be inserted, updated, or deleted at the server. Depending on the error, it might be very important to know the current state of the data at the server. The application must be built to handle this situation, because deleting the tracked table deleted the error table.

Errors and Nonbatch Transactions

During nonbatch transactions (BATCHINGOFF option when you use the Push method), conflicts are detected at the row level. The conflicting row is returned to the application and stored in a specified error table. For example, if the application tries to push a row to SQL Server that is not valid, that row is returned to the application and stored in the error table together with an error message indicating the conflict.

When a conflicting row is returned to the error table, that row is removed from the original table. Because the table is not left in its original state, it is slightly more difficult to resolve the conflict that has occurred. You must design the application to let the user correct the conflicting data. To do this might involve repulling the table from the server to correctly resolve the situation.

Dropping the table on the device will cause the error table to be dropped. You must either cache the rows in the error table in a temporary location or pull the data from the server into a different table. Because the offending rows are moved out of the table on the SQL Server Compact Edition database, it is important that the table is refreshed again with the correct server data. If the row that failed was originally updated, it must be an update to the same row again for it to succeed on the subsequent push. If the row was updated, but the row on the server was deleted, the row must be added back into the table and pushed again for the insert to succeed.

Errors and Batch Transactions

RDA also supports a batch push (BATCHINGON option when you use the Push method) that requires all rows to succeed for the complete push to process. If one row fails, the complete push transaction fails and no data is updated. The conflicting rows are copied to the error table. This is the preferred option, because it enables a slightly easier mechanism to resolve the conflict. Unlike the nonbatched push, the original Microsoft Windows CE-based database remains intact. You must design the application to let the user correct the conflicting data and merge it back into the original Windows CE-based database. Because the original row remains intact, depending on the error, it might not be required to immediately repull the server data to determine the correct resolution of the row. For example, if the row failed because of integrity violation, the row on the device can be updated and the Push method invoked to try to push the data to the server. This option also provides a cleaner maintenance, because the error table is automatically cleaned before a conflicting row is copied. Only the conflicts from the last push operation exist in the table.