{Article updated towards the end: There is no issue with change tracking here}

Microsoft Sync Framework provides synchronization capabilities between diverse data stores with the ability to implement custom sync providers targeting a multitude of stores.

While working on the sync framework we have the option of using SQL Express or SQLCE as the client side database sync provider. In the solution currently being implemented in our application we used SQLCE 3.5 SP 1 as the client side database store.

Sometime back one of my team members informed us that there was a bug with SQLCE related to the entity framework. There was a hot fix to resolve this issue so we all chose to download and install the hot fix.

Its been a few months since then and while performing a few tests on the sync solution implemented by us I noticed that the client side change tracking was not getting updated as inserts and updates were manually being performed on the SQLCE db.

On closer inspection I figured out that all upload related sync tests were failing whereas downloads were happening fine.

Here are the steps I performed some of which passed and some failed: (those who have worked with the sync framework should be able to understand these)

  1. Create an sdf on the client.
  2. Create a table on the client sdf and populate with data.
  3. Set TableCreationOption to UploadExistingOrCreateNewTable
  4. Run a sync - verify if data has appeared on the server – YES - This happened successfully.
  5. Verify that the client table has been modified for changetracking – YES – values shown are zeros in both change tracking columns.
  6. Insert a row into client table - the change tracking columns show NULL for the inserted row.
  7. Update rows in the client table. - verify if the change tracking values change – NO- values remain as zeros for the updated row.
  8. Run a sync - verify if data is uploaded to server – NO – data is not uploaded to the server.
  9. Drop table on the client and delete all rows from the server table. (My attempt to clean up the client & server and start afresh).
  10. Set TableCreationOption to DropExistingOrCreateNewTable
  11. Run a sync - verify that schema has been created on the client – NO – Error during sync – details below (could not proceed with remaining steps)
  12. Insert data into client - check the change tracking values.
  13. Run sync - verify upload of inserted data.
  14. Update data in the client - verify the change tracking values.
  15. Run sync - verify upload of updated data.

The error faced during step 11:

Invalid column ID. [ __sysChangeTxBsn ]

Source :

SQL Server Compact ADO.NET Data Provider

StackTrace :

   at System.Data.SqlServerCe.SqlCeChangeTracking.ProcessResults(Int32 hr)

   at System.Data.SqlServerCe.SqlCeChangeTracking.DisableTracking(String tableName)

   at Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.DisableOcsTracking(String tableName)

   at Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.EnableOcsTracking(String tableName, SETRACKINGTYPE seTrackingType)

   at Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.CreateOcsTable(String fmtTableName, Boolean createTable, SyncTable syncTable, SyncSchema syncSchema, SqlCeCommand cmdUtil)

   at Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.CreateSchema(SyncTable syncTable, SyncSchema syncSchema)

   at Microsoft.Synchronization.SyncAgent.InitClientSchema()

   at Microsoft.Synchronization.SyncAgent.DataSynchronize()

   at Microsoft.Synchronization.SyncAgent.Synchronize()

Since there hadn’t been many code changes since the last time the tests had worked I guessed that SQLCE was playing its part in these errors. With some help from the sync framework team I figured that I was using a version of SQLCE that was higher than that being used by the sync framework team (also the one bundled as SQLCE 3.5 SP 1).

I then reverted my SQLCE version to 3.5.5692.0 and found that the issue did not exist any longer.

If you face any issues related to change tracking on the SQLCE database – the __sysChangeTxBsn or __sysInsertTxBsn columns don’t get modified as you update or insert values into the database, then make sure your version of SQLCE isn’t 3.5.5692.1.

Issue solved…Sync framework upload scenario working successfully.

Cheers!!!

Sidharth

 

[Update: Sidharth]

This issue occurs only if you have an existing connection to the SQLCE DB open before the table in concern is configured for change tracking. It looks like the open connection does not recognise that change tracking has been enabled and treats the table as a normal table.

The result of this is that even if the table has been configured for change tracking, if you use the same connection to update the table, the __sysChangeTxBsn or __sysInsertTxBsn do not get modified.

This issue does not occur if you open a connection to the sdf after running sync once (basically get the table enabled for change tracking before you open a connection to the DB and start updating records).

No issues with SQLCE.