Change Tracking in SQL Server 2008 - sys_change_context Showing last result only - in bi-directional sync RRS feed

  • Question

  • Hi,

    I'll try to break down the problem to the core issue, even though this is slightly complex:

    1. Two Databases, A and B, participating in a bi-directional sync.

    2. Database A performs an insert to a record, which receives ID of #11.

    3. A sync from A to B occurs, causing database B to register an "I" operation. It registers the "I" operation with context='NoSync' in order to prevent echoing. Note that NO sync from B to A occurs at this point yet.

    4. Database B now UPDATES the new record (#11).

    5. A sync from B to A occurs. In order to query for changes since last version, database A looks at B and requests changes using the CHANGETABLE() approach. It is my understanding that, by design, database B will report the following info: [ID=11, sys_change_operation=I, sys_change_text=null]. Since it provides the net changes only, it will show this record as an INSERT, despite our efforts to "hide" the insert by setting the Context when we inserted the record into database B at the first sync.

    6. The result now - Database A will try to INSERT record #11, since that's what it sees. Of course, this record already exists and this won't work.

    *My Question Is:*

    How am I supposed to do this? How can I sync from database B to A, after a change has been done to a record in database B right after the insert, and before the sync back?

    I cannot seem to find a way for me to get only the "U" here, since CHANGETABLE() will try to be "smart" and give me the net changes.

    Friday, December 31, 2010 6:31 AM


  • sync uses the @sync_last_received_anchor as the last_sync_version value to the CHANGETABLE function so the client should get the right CHANGE_OPERATION if you are running with Sync Services. you can pass in the right version and see if you can get the U as change operation with your case. e.g.



    * from changetable ( changes <your table>, <your current sync version> ) as CT

    for more information about the queries sync uses to eumerate the changes from the server side, pleaes refer to the section in sync service BOL http://msdn.microsoft.com/en-us/library/cc305322.aspx

    if you still see issues where the incorrect change operation is returned, can you provide details about your application and the queries you used at the server provider ? and also the RowState of the rows in ChangesSelect event at the server provider ?



    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by Yunwen Bai Friday, December 31, 2010 9:54 PM
    Friday, December 31, 2010 9:54 PM