Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Change tracking is not updating sys_change_operation to U when i update a particular row. RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    I'm just exploring the feature Change tracking on SQL EXPRESS 2008 version. I'm able to see the sys_Change_Operation value I and D when I insert or delete a row. When I make update call to the change track enabled table, I never see the value to U for Sys_Change_Operation. But the Sys_Change_Version and Sys_Change_Creation_Version are all properly updated for the updated row with the proper values. But the Sys_Change_Operation still show as I. Am I missing any settings?

    I used the following comments to enable the change tracking.

    Alter Database testDB set allow_snapshot_isolation ON
    alter database testDB set change_tracking = on (CHANGE_RETention = 4 days, auto_cleanup = on)
    alter table wa.tblMyData enable change_tracking WITH (TRACK_COLUMNS_UPDATED = ON);
    SET TRANSACTION ISOLATION LEVEL SNAPSHOT

    Thanks,

    Naina Mohamed

    .NET developer
    Tuesday, February 22, 2011 3:46 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    How are you looking up the operation? Are you looking at CHANGETABLE (CHANGES...)?

    If so, what value are you passing in for the version? If you pass 0, then it will be seen as an INSERT with the latest data though.

    However if you pass in a version which is greater than 0 and greater than the insert time, but less than the update time, you should see UPDATE being returned.

    Additionally you could look up the change tracking tables (by querying sys.internal_tables) and look into the contents of the 'sys.change_tracking_nnnnn' table and see that it is being tracked as an UPDATE.

    This posting is provided AS IS with no warranties, and confers no rights
    Tuesday, February 22, 2011 6:44 PM