How to download data to local store that was not part of sync session ? RRS feed

  • Question

  • If i have some tables being inserted via scripts it would not have the correct timestamps for the sync framework to download the incremental changes.

    The scenario happens when there is a legacy system which has large amounts of data and those data were migrated to the database that has download only sync tables. For some reason it doesn't download the migrated data. Here is my tracking column configuration.

    Read as [ColumnName - DataType - Allow nulls - Default value or binding]


    UpdateTimestamp - timestamp - True

    InsertTimestamp - binary(8) - True - Default value or binding = (@@dbts+(1))

    InsertID - UniqueIdentifier - True  -  Default value or binding = ('00000000-0000-0000-0000-000000000000')

    UpdateID - UniqueIdentifier - True - Default value or binding = ('00000000-0000-0000-0000-000000000000')


    Will the data get downloaded even when it doesn't pass through the sync session (via application) ?


    • Edited by Viswanth Tuesday, September 20, 2011 12:36 AM
    Sunday, September 18, 2011 5:05 AM

All replies

  • am assuming you're using the offline providers (SqlCeClientSyncProvider/DbServerSyncProvider), since its based on timestamp, you should be able to download changes even if theyre made not via the sync application since the change tracking is done at the database level and not at the application level.

    how are you doing the migration? do you have any filters?

    Tuesday, September 20, 2011 2:26 AM
  • Yes., i'm using SqlCeClientSyncProvider and DbServerSyncProvider providers. Migration is through scripts. I don't specify any value to tracking columns in the script. That is the reason why i'm allowing nulls in the table's tracking columns.

    All I use is is the Incremental Inserts, Updates, Deletes command based on timestamp.

    //Incremental Inserts

      IncrInserts.CommandText = "Select * FROM  Customers C" +                                        
                                              " Where C.InsertTimestamp > @" + SyncSession.SyncLastReceivedAnchor +
                                              " AND C.InsertTimestamp <= @" + SyncSession.SyncNewReceivedAnchor +
                                              " AND C.UserID IN( '" + loggedInUser + "')"; //my application variable

    IncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);

    IncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);

    IncrInserts.Connection = serverConn;


    //Incremental updates

    IncrUpdates.CommandText = "Select * FROM  Customers C" +                                               
                                                     " WHERE (C.UpdateTimestamp > @" + SyncSession.SyncLastReceivedAnchor +
                                                     " AND C.UpdateTimestamp <= @" + SyncSession.SyncNewReceivedAnchor +
                                                     " AND NOT (C.InsertTimestamp > @" + SyncSession.SyncLastReceivedAnchor + ")" +
                                                     " AND C.UserID IN('" + loggedInUser + "'))";

    IncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);

    IncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);

    IncrUpdates.Connection = serverConn;


    I'm selecting new anchor value

     SqlCommand selectNewAnchorCommand = new SqlCommand();
                string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
                selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1";
                selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
                selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
                selectNewAnchorCommand.Connection = serverConn;
                this.SelectNewAnchorCommand = selectNewAnchorCommand;


    I'm not using any of the conflict management stored procedures. Is there a guide for configuring the database for custom tracking ?


    Tuesday, September 20, 2011 8:56 AM
  • see this link on how to do custom change tracking: http://msdn.microsoft.com/en-us/library/cc305973(SQL.110).aspx

    just a question, why do you need custom change tracking? and why dont you consider using SqlSyncProvider instead?

    Wednesday, September 21, 2011 1:16 AM
  • I'm trying both the options to see which is easier to implement. Can you share some links for setting up SQLSyncProvider ?

    By looking at the table, the tracking columns that i use especially the InsertId and UpdateId columns' always have default values. Should i need to set any specific value while inserting records or the sync session would set values for those columns ?

    Thursday, September 22, 2011 1:47 AM
  • have a look here on how to use SqlSyncProvider: http://msdn.microsoft.com/en-us/library/bb902853(SQL.110).aspx

    are you actually  using the InsertId/UpdatedId in your sql commands? the Id's are normally used to identify which client made the updated. for purposes of change tracking, the timestamps would have sufficed.

    Thursday, September 22, 2011 2:12 AM
  • Thanks JuneT.

    I have implemented SqlSyncProvider and have followed the steps mentioned. Timestamps are used to identify when data was inserted. There is one scenario where my deleted data comes back to local store in the next sync. I was curious to know the reason and i tried to implement the Incremental Inserts/Updates/Deletes for a BiDirectional sync table. In of the links (http://msdn.microsoft.com/en-us/library/bb726041%28v=SQL.110%29.aspx) the tracking columns used where InsertID, UpdateID. These columns where then used in the Incremental Inserts and Updates command

    IncrementalInserts command - "WHERE InsertId <> Sync_ClientId" and

    IncrementalDeletes command - "WHERE DeleteId <> Sync_ClientId" .

    This is just to identify which client has inserted/deleted data so that it won't consider this record during the sync for this client.

    I was not clear on the values in this column. In the table it is all default to 00000000-0000-0000-0000-000000000000. How would it find which client inserted/deleted the record ? I think becoz of this, the data is getting downloaded again after being deleted from the client.

    Friday, September 23, 2011 2:19 AM
  • the SqlSyncProvider uses its own tracking mechanism and will not require you to create timestamp fields. it uses triggers to keep track of changes in the _tracking tables.

    if you're using a custom change tracking, the InsertId and UpdateId is defaulted to 0s to indicate the change has occured from the server and not from a client (read: direct insert or update on the server itself and the change didnt come from synching). sync framework should take care of passing a ClientId during the sync.

    here's a link for using SqlCeSyncProvider/SqlSyncProvider without having to worry about those timestamps and clientids: http://msdn.microsoft.com/en-us/library/ee617382(SQL.110).aspx



    Friday, September 23, 2011 2:53 AM