locked
Change tracking in SQL CE on a Windows Mobile client RRS feed

  • Question

  • I have a project up and running whereby I have a WCF Sync Service set up with a SQL Express database, and a Windows Mobile 6 client using SQL CE, which is syncing to the WCF service.

    I have two tables on the client at the moment which don't get updated by the client, but which do get updated on the server, so these are 'download only' when syncing - these seem to work fine.

    I have added a table on the client database which will get updated by the client, but not by the server, so these are 'upload only' when syncing. However, records from this table are not going across.

    I have often wondered about how any change tracking metadata is stored on the client as I don't get to set up any SyncAdapters for the tables in the client code. I have done some searching and found this article (http://alexduggleby.com/category/sync-framework/) which suggests that the change tracking is a built in feature in SQL CE that the sync framework uses.

    That being the case, I queried my SQL CE database and noticed that two columns had been added to my table schema: __sysChangeTxBsn and __sysInsertTxBsn.  However, for each row in the table, these column values are NULL.

    Some more searching turned up this article (http://blogs.msdn.com/sidharth/archive/2009/04/23/change-tracking-fails-for-sqlce-version-3-5-5692-1.aspx) which suggests that there is an issue with using SQL CE 3.5 SP1 with the (compact) sync framework.

    So my questions are:
    Should I be using a specific version of SQL CE with the sync framework (when using the compact framework version)?
    Should I be populating the __sys***TxBsn columns or should it be done automatically by SQL CE?
    Is there an offical (MSDN?) document which details how the Sync Framework works with SQL CE change tracking, specifically the __sysOCSDeletedRows, __sysOCSTrackedObjects, __sysSyncArticles, __sysSyncSubscriptions and __sysTxCommitSequence tables?

    Many thanks in advance if you can help.
    • Edited by P.Mc Wednesday, August 12, 2009 8:50 AM Clarify question
    • Moved by Max Wang_1983 Friday, April 22, 2011 12:00 AM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Wednesday, August 12, 2009 8:34 AM

Answers

  • Hi Paul,
    To answer your first question of the upload only tables not having change tracking info.

    If you enable change tracking on a CE database that already has data in tables then the change tracking data will not be available for those rows. To give them versions you would have to do a dummy update which will touch all rows thereby triggering the change tracking for them.

    To answer your second question - Download only tables dont get the values after you manually deleted them from client.
    Once you sync a table from a server the client stores the "Last Received Anchor" value for that table. This basically says what is the last server tickcount that the client knows about. In the next sync only rows modified after that anchor value will be downloaded. If you delete all rows on client the sync runtime doesnt know about it (coz its download only) and since the received anchor value hasnt been reset you dont get the changes back. The only way to get the changes back is if you did a "Snanshot" sync for that table or you deleted the "LastReceivedAnchor" for that table.
    Maheshwar Jayaraman - WCF -- http://blogs.msdn.com/dotnetremoting
    • Marked as answer by P.Mc Thursday, August 13, 2009 8:49 AM
    Wednesday, August 12, 2009 11:27 PM
    Moderator

All replies

  • Some more info:

    I've wiped the records from the client table that is not uploading correctly, and reinserted some new records - these now have the __sysChangeTxBsn and __sysInsertTxBsn column values auto populated.

    So it seems that what happended is this:
    1) I created the new table, without adding in the __sysChangeTxBsn and __sysInsertTxBsn columns.
    2) I inserted some records into this table on the client.
    3) I initiated a Sync - the records didin't go across, but now the table has been updated to add the __sysChangeTxBsn and __sysInsertTxBsn columns, albeit with NULL values for existing records.
    4) I deleted the records from the table and inserted new records.
    5) The new records are automatically given values for the __sysChangeTxBsn and __sysInsertTxBsn columns.
    6) I initiated a Sync and the records are uploaded to the server.

    So now I'm wondering:
    Do I have to add the __sysChangeTxBsn and __sysInsertTxBsn columns when creating a new table on the client?
    Or, do I run something against the client database that will create these columns in the table and *register* the table for change tracking?
    Wednesday, August 12, 2009 9:14 AM
  • More info:

    I deleted the records from the 'download only' client tables and initiated a sync. I expected the tables to repopulate with the data from the server, but they remain empty.

    I've stepped through the sync progress and can see that there are no changes being passed across for these tables, as if they remain in sync.

    This suggests to me that the sync metadata has not updated for these deleted records, and therefore the sync orchestrator does not consider any changes necessary.

    Using Query Analyser for SQL CE, I queried the contents of the __sysOCSDeletedRows table and there are entries for all the rows deleted from the two tables (I tried to delete these records but am blocked from doing this "Data cannot be deleted from a system table or information schema view").  Same applies for updating any of the tracking tables.

    How do I sort this mess out?

    Help please!
    Wednesday, August 12, 2009 2:59 PM
  • Hi Paul,
    To answer your first question of the upload only tables not having change tracking info.

    If you enable change tracking on a CE database that already has data in tables then the change tracking data will not be available for those rows. To give them versions you would have to do a dummy update which will touch all rows thereby triggering the change tracking for them.

    To answer your second question - Download only tables dont get the values after you manually deleted them from client.
    Once you sync a table from a server the client stores the "Last Received Anchor" value for that table. This basically says what is the last server tickcount that the client knows about. In the next sync only rows modified after that anchor value will be downloaded. If you delete all rows on client the sync runtime doesnt know about it (coz its download only) and since the received anchor value hasnt been reset you dont get the changes back. The only way to get the changes back is if you did a "Snanshot" sync for that table or you deleted the "LastReceivedAnchor" for that table.
    Maheshwar Jayaraman - WCF -- http://blogs.msdn.com/dotnetremoting
    • Marked as answer by P.Mc Thursday, August 13, 2009 8:49 AM
    Wednesday, August 12, 2009 11:27 PM
    Moderator
  • Hi Maheshwar - thank you for your reply.

    It makes perfect sense how the existing download only tables don't have any Change Tracking info - these were added before CT was turned on. Problem is, I think the Sync Framework turned CT on and I can't find any SQLCE commands to control it (there's plenty of info for SQL 2008, but the same commands don't seem to work in CE).

    I would ideally like to delete the LastReceivedAnchor for each table that gets deleted from, but can't update the records in the __sys*** tables as I get an error that they are "readonly system tables".

    Do you know of any way to control Change Tracking in SQLCE?

    Also, anyone on the SF team know of the compatibility issues between SQL CE 3.5 SP1 and the Sync Framework? Should I be using the non-service pack version of 3.5?
    • Edited by P.Mc Thursday, August 13, 2009 8:59 AM to fix spelling mistakes!
    Thursday, August 13, 2009 8:55 AM