locked
How can I tell if a row has unresolved changes with Sync 2.1 / SqlSyncProviders? RRS feed

  • Question

  • Hi all,

    Our existing solution works well synchronizing between a pair of Sql Server databases.  In this implementation we are using SqlSyncProviders for both databases, and only one (the 'client') actually performs synchronization.

    There is a specific kind of change we detect and want to be able to abort before it is committed to the database if there are unresolved changes on the row.  So, in Table A, row 1, the user tries to modify a certain column on the 'server'.  We want to ask the client if it has any changes that it believes need to be synchronized, and if so we're going to stop the change from going forward.

    I found a blog entry (http://blogs.msdn.com/b/sync/archive/2011/03/02/synchronization-services-for-ado-net-for-devices-improving-performance-by-skipping-tables-that-don-t-need-synchronization.aspx ... under "Get the changed tables from the server side") that seems to be close to what I want to do, but it uses objects I am not familiar with.  In particular, I don't know how to determine the ClientId or the LastReceivedAnchor values that are needed to call GetChanges.

    Any suggestions are very much appreciated!

    -Kevin

    Tuesday, November 8, 2011 4:56 PM

All replies

  • the link you reference above is for a different set of database providers (offline providers SqlCeClientSyncProvider/DbServerSyncProvider).

    do you want to intercept the changes from the client before it get's sent and applied to the server? you can subscribe to the ChangesSelected even on the client provider and evaluate the rows.

    something similar to this approach: Manipulating the change dataset in Sync Fx

    Wednesday, November 9, 2011 4:47 AM
  • Hi June,

    I do realize they are a different set of providers ... but it is an example of what I need to do, ultimately.

    I already know how to intercept changes (we do that for a different purpose).  What I need to do know is actually stop a user from changing a value, on either side of the sync relationship, if there are changes that have yet to be synchronized.  It's an unusual requirement, I know.  It's not even something we will do often (I never like to throw errors at a user for something they have no control over).  

    The problem is very complex and while I could articulate it, it would take a lot of text and wouldn't contribute to the actual solution.

    I am open to any ideas on how I can tell if the tracking row indicates there are changes to be looked at.  I can look directly at the row, if I knew enough information, but regardless of how I do it I think the outstanding issue is how I discover the last received anchor.

    Overnight I got to thinking that I would need to read in the knowledge of the scope_info table for the relevant scope (we have many) and that I could get the anchor value from that, but its a guess at this point.  I've never done that before, either (as it isn't something one would normally do when using SqlSyncProvider).

    -Kevin

    Wednesday, November 9, 2011 12:54 PM
  • unfortunately, the sqlsyncprovider works differently than the offline provider. in the offline provider, it's just the client that stores the anchor values for the lastreceived and lastsent. the server knows nothing about what has been sent or received. likewise, the client doesnt even keep track of each individual row it synched, just the anchor values.

    in the sqlsyncprovider, each replica stores the "knowledge" of what has been synched and from which replica. so to get the changes from the client that hasnt been synched to the server, the server would actually send its "knowledge" to tell the other replica what it already knows. it's like having a two way pass, the client determines what changes it has and then checks if these changes are known to the server already.

    here's one workaround if you just want to know if a row has changed since the last time you sync.

    every time you sync, store the date and time for that sync. now, on the _tracking tables, there is a last_change_datetime column.

    you can compare value of this column to the last sync time that you stored.


    • Edited by JuneT Wednesday, November 9, 2011 1:26 PM
    Wednesday, November 9, 2011 1:23 PM
  • Thanks June,

    That might be my backup plan, then ... but since this product is already in production I prefer to find a way that doesn't involve changing the schema.

    I appreciate that each server sends knowledge to its partner replica and its through that knowledge that the change set is determined, but at some point it does boil down to running the _selectChanges stored procedure that sync generated.  If I can better understand the four values provided to that stored procedure then I can simulate the query for my own purposes.

    I suppose I could always have the server attempt to sync that row and check what it wants to change ahead of time, similar to your first suggestion.

    Thanks for the ideas,

    -Kevin

    Wednesday, November 9, 2011 1:45 PM
  • one of the values passed to the _selectchanges sp is the sync min timestamp which is derived from the sync knowledge of the replica requesting the changes.

    again, if you just want to get the rows that have changed and are to be sent to the other replica, use the ChangesSelected event and just throw an exception to abort the sync.

    Wednesday, November 9, 2011 11:44 PM