locked
Missing update information on changetable RRS feed

  • Question

  • Hi all,

    My app used Local Database Cache template to sync a SQL Server 2008 R2 database and SQL CE 3.5. The sync (Bidirectional) was working properly until the day I found some changes didn’t get downloaded into client database.

    I thought there was a problem on my SelectIncrementalUpdatesCommand but apparently not. I did find the changes were not listed on the changetable (no record of SYS_CHANGE_OPERATION = 'U'). It is strange as the Change Tracking didn’t track the changes.  It tracked the insertion (that was created some days ago) only.

    I'm unsure what is happening and how to solve it. As per my understanding, there is no way to interfere the Change Tracking as it is like a black-box tech.

    Retention period has not reached its cutoff period and I didn't reenable Change Tracking on the database.  So in this case, all the tracking information persisted.

    Do you have any suggestion?

    Thanks,


    Agung

    Thursday, October 28, 2010 7:25 AM

Answers

  • Hi Agung

    The results you are reporting are correct and as expected as far as change tracking is concerned.

    After the UPDATE you should still get one row - change tracking aggregates results and always returns one row per changed row, no matter how many time the row has changed.  Your getting an "I" in the second query because you are still passing in 0 as the last_sync_version.  Passing 0 indicates you want all changes that have happened since 0; an Insert and Update happened, but the net-change is still an insert.  The 0 indicates you haven't seen any operations yet for the row, you don't know the row exists yet, and therefore it returns an "I".

    Try the following:

    • select * from changetable(changes [tablename],1789579) a

    You should get back a "U".  This is because your last_sync_version is indicating you have "seen" the Insert and will therefore report that the net-change is an update.

    So I believe change tracking is behaving correctly; it always returns the single net-change for any row that has been changed, no matter how many times it has been changed; it always reports the change relative to the last_sync_version.  CHANGETABLE(CHANGES ...) is basically saying - I've seen all changes up to last_sync_version; give me the net-change for each row that has been changed after last_sync_version.

    This hopefully explains the change tracking behavior.  We still need to work out why changes did not get downloaded though!

    Thanks, Mark

    Friday, October 29, 2010 2:54 PM

All replies

  • Hi Agung

    You can try to work out if the Update was tracked by doing the following:

    • Select from CHANGETABLE(CHANGES ...) passing a last_sync_version of NULL and the primary key of the row in question.  This will return one row specifying an operation of Insert (assuming the insert happened within the retention period).  Note the SYS_CHANGE_VERSION.
    • Select from CHANGETABLE(CHANGES ...) passing a last_sync_version of SYS_CHANGE_VERSION - 1.  If the update was noted then this should return the a SYS_CHANGE_OPERATION of "U".  If only an Insert was seen then this would still report "I".  What do you see?

    It is extremely unlikely that the update is not being tracked.  If an Update was performed after the Insert, but the last_sync_version is not brought forward to after the Insert operation, then an "I" will be returned as CHANGETABLE(CHANGES ...) does not think you've seen the original Insert.

    You can also look at the raw operations in in the internal change table to see if the Update was tracked.  I specified how to do this in the other thread.

    Thanks, Mark

     

    Friday, October 29, 2010 3:14 AM
  • Hi Mark

    Thank you for replying.

    For easier examination, I cleaned up all the tracking information by reenabling the Change Tracking. There is no trigger involved. Then I do these directly on the main database (using SSMS) to see if the change tracking works properly:


    1. select CHANGE_TRACKING_CURRENT_VERSION.
     -> I got 1789578
    2. select * from changetable(changes [tablename],0) a
     -> return no record
    3. insert a new record to the table
     -> a new row inserted
    4. select * from changetable(changes [tablename],0) a
     ->There is one row returned, i.e.:
     SYS_CHANGE_VERSION : 1789579
     SYS_CHANGE_CREATION_VERSION : 1789579
     SYS_CHANGE_OPERATION : I
     SYS_CHANGE_COLUMNS : NULL
     SYS_CHANGE_CONTEXT : 0x6167756E67
     FieldID: 9
    5. select CHANGE_TRACKING_CURRENT_VERSION()
     -> I got 1789579.
    6. update the new inserted row
     -> 1 row updated
    7. select * from changetable(changes [tablename],0) a
     -> There is still only 1 row as below:
     SYS_CHANGE_VERSION : 1789580 
     SYS_CHANGE_CREATION_VERSION : 1789579
     SYS_CHANGE_OPERATION : I
     SYS_CHANGE_COLUMNS : NULL
     SYS_CHANGE_CONTEXT : 0x6167756E67
     FieldID: 9

    The SYS_CHANGE_VERSION is increased but the SYS_CHANGE_OPERATION is still persisted as I instead of U. It is the condition I said the update didn't get tracked. Where is the U? 

    Running the SelectIncrementalUpdatesCommand returns no row in this circumstance (It should be 1. Am I correct?).

     

    Agung

     

    Friday, October 29, 2010 8:57 AM
  • Hi Agung

    The results you are reporting are correct and as expected as far as change tracking is concerned.

    After the UPDATE you should still get one row - change tracking aggregates results and always returns one row per changed row, no matter how many time the row has changed.  Your getting an "I" in the second query because you are still passing in 0 as the last_sync_version.  Passing 0 indicates you want all changes that have happened since 0; an Insert and Update happened, but the net-change is still an insert.  The 0 indicates you haven't seen any operations yet for the row, you don't know the row exists yet, and therefore it returns an "I".

    Try the following:

    • select * from changetable(changes [tablename],1789579) a

    You should get back a "U".  This is because your last_sync_version is indicating you have "seen" the Insert and will therefore report that the net-change is an update.

    So I believe change tracking is behaving correctly; it always returns the single net-change for any row that has been changed, no matter how many times it has been changed; it always reports the change relative to the last_sync_version.  CHANGETABLE(CHANGES ...) is basically saying - I've seen all changes up to last_sync_version; give me the net-change for each row that has been changed after last_sync_version.

    This hopefully explains the change tracking behavior.  We still need to work out why changes did not get downloaded though!

    Thanks, Mark

    Friday, October 29, 2010 2:54 PM