SQLCE 3.5 SP2 Detecting Changes

Question

Hi,

I am using sync framework to synchronize an SQL CE 3.5 SP2 database with SQL Azure over WCF. That is working fine.  What I would like to do is to detect any local inserts or updates (in the SQLCe DB) since the last syncrhronization. I have read the following article in SQLCE books online: ms-help://MS.SSC.v35/MS.SSC.v35.EN/sscprog/html/5be071e5-41c9-4775-85d4-a41d6a370fe7.htm that details the change tracking in SQLCE, but I am a little confused as to where/how I can retrieve the @LCSN value.

Previously, this value could be obtained from the __sysSyncArticles tables. However, this table is no longer present with SP2.

I would be very grateful if any could shed some let on how I can detect unsyncrhonized changes on the client DB.

Thanks,

Ian

Answer 1

Hi IByrne,

>>__sysSyncArticles tables. However, this table is no longer present with SP2.

Based on my research, the table __SyncArticles is user tables, not system tables.
If a database contains the __sysSyncArticles  table then it is compatible with Sync Framework 1.0 and earlier versions. So what version of sync framework you are using?
For more information, please refer to here: http://technet.microsoft.com/en-us/library/system.data.sqlserverce.sqlcechangetracking.upgradepublictracking(v=vs.97).aspx

>>how I can retrieve the @LCSN value.

__sysSyncArticles  is an internal tables that keeps a list of tables being tracked for changes along with each table's sync.
How to retrieve if table has pending changes or not, the most important part is the @LCSN which is the watermark for the last received anchor.
How to retrieve please refer to the code from this article: Sync Inspector Tool - Inspecting Client DB state for pending Changes http://blogs.msdn.com/b/agujjar/archive/2008/11/22/sync-inspector-tool-inspecting-client-db-state-for-pending-changes.aspx

>>how I can detect unsyncrhonized changes on the client DB.

However the data not synchronizing or behaving erroneously. There are several tracking columns in SQL CE that are used for SQL Merge Replication and not for Sync Services use.
If you don’t want to synchronized changes to server once last synchronize. You can set your client database disable user to write.

Additional, you can also refer to these articles:
1. How to: Configure and Execute Synchronization with SQL Azure
http://msdn.microsoft.com/en-us/library/ff928617%28v=SQL.110%29.aspx
2. Extending SQL Azure data to SQL Compact using Sync Framework 2.1: http://blogs.msdn.com/b/sync/archive/2010/09/13/extending-sql-azure-data-to-sql-compact-using-sync-framework-2-1.aspx

---

Regards,
Amber zhang
TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

Answer 2

Hi Amber,

Thanks for the reply.  I am using sync framework version 2.1 and the sync to SQl Azure is working fine. I believe the issue I am having is that I'm using version 2.1 of the sync framework, as all of the links I've found so far (including the sync-inspector tool link you provided) are using an older version of the sync framework.

I have read the articles you linked to, but my SQLCE 3.5 SP2 database does not contain the tables _SyncArticles or _SyncSubscriptions.

The only sync related user tables that have been created by the sync framework are:

schema_info
scope_config
scope_info

In the scope_info table I have the following columns:

scope_local_id; scope_id; sync_scope_name; scope_sync_knowledge; scope_tombstone_cleanup_knowledge; scope_timestamp; snapshot_timestamp; scope_config_id; scope_user_comment;scope_tomb_mark

To me it looks like the column scope_timestamp might be related to the last sync - but I am unsure if/how this can be used as the @LSCN

If you could find out anything about this scenario that would be great.

Thanks,

Ian

Answer 3

From looking at the sync trace log the value that is being used in the query is coming from the scope_tomb_mark column in the scope_info table.

This appears to be what the sync framework 2.1 is using. Can anyone confirm this?

Answer 4

Hi IByrne,

Thank you for your update. Based on my research the scope_info table, which is used to copy the syncknowledge from another scope to try and trigger some action on sync.
For more information about scope_info, please refer to this article. http://msdn.microsoft.com/en-us/library/bb726013.aspx

According to your goal you can try to call sync, the just the ChangesSelected on the local provider to determine if the sync will continue or not, based on if there is any changes enumerated as Yunwen Bai mentioned in this post, which provided the solution about how to detect if the client has data which has not been synchronized yet http://social.microsoft.com/Forums/eu/syncdevdiscussions/thread/1b86ffbf-3b33-4c35-9267-66fe93db5a09

Additional you can refer to SQL Server to SQL Azure Synchronization using Sync Framework 2.1 http://blogs.msdn.com/b/sync/archive/2010/08/31/sql-server-to-sql-azure-synchronization-using-sync-framework-2-1.aspx

Hope it is helpful.

---

Regards,
Amber zhang
TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedbackhere.