Share via

Change tracking fails for sqlce version 3.5.5692.1 during upload/bidirectional scenario

  • Article

{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.

Comments

  • Anonymous
    April 23, 2009
    PingBack from http://asp-net-hosting.simplynetdev.com/change-tracking-fails-for-sqlce-version-3556921-during-uploadbidirectional-scenario/

  • Anonymous
    May 04, 2010
    Hi, really interesting post. I think I might have an app that is getting this same problem with sync groups not uploading changes and tracking tables looking the culprit. I don't quite understand the cause, or your resolution. In my app, I have several other tables in addition to sync tables. When the app starts, I connect to the sdf, create app tables and then do a sync. Are you saying that I should close the app's db connection before doing the initial sync?

  • Anonymous
    May 07, 2010
    Hi Jon, Yes. You need to close the connection to the sdf before running sync. If the sdf was not configured for change tracking before you make a connection, and then whilst keeping the connection open you run a sync, then the connection does not recognise this change. To prevent this close your connection, perform sync, reopen the connection and continue. In my case I discovered this because I left a connection open through Sql server management studio. Sidharth