Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
WITH CHANGE_TRACKING_CONTEXT kept SYS_CHANGE_CONTEXT as NULL RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    I'm running SQL Server 2008 with Change Tracking enabled.

    I find SYS_CHANGE_CONTEXT containing NULL value although WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary) had been called. I'm expecting the value as @sync_client_id_binary.

    I run the script on Query Analyzer to ensure everything is fine.

    Here is the snippet
    ;WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary)
      INSERT INTO [dbo].[Address] ([AddressID], [Name], [Address1], [Address2], [LastEditDate], [CreationDate])
      VALUES (@AddressID, @Name, @Address1, @Address2, @LastEditDate, @CreationDate )

    I'm sure that @sync_client_id_binary is not NULL.

    That problem is confusing as I got the expected result (SYS_CHANGE_CONTEXT containing @sync_client_id_binary) when I run the script on another server (with the same spec).

    How to get the SYS_CHANGE_CONTEXT set? Could somebody help please. Thanks

    Agung

    P.S.

    I'm unsure if this information helpful: I checked CHANGE_TRACKING_CURRENT_VERSION of both the databases.  It is the result:

    - the database which the problem occured: 5944

    - the other database (with the expected outcome): 3

    I'm wondering if value of CHANGE_TRACKING_CURRENT_VERSION (that so big?) caused the problem.

    Thursday, August 5, 2010 5:29 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Agung

    The values of CHANGE_TRACKING_CURRENT_VERSION will differ greatly between databases and have nothing to do with the problem.  The "other database" is probably new with few updates and the value is therefore low; the database which reported 5944 will have been around for longer and have had a lot more transactions executed on it.

    The only thing I can think that would cause your problem is that there is a further operation being performed on the same row after the insert and before you query for changes.  Is there a trigger on that table on that database?  Could there be a further update after the insert?  Can you run the profiler to see what is happening?

    Do the above first.  If that does not reveal anything then another thing to try is to dump out the contents of the internal tracking table, which will list all the operations to that row.

    • Login using a dedicated admin connection (see books online for further info)
    • Find the name of the tracking table using sys.internal_tables
    • Select from the internal table for your Address table - you should be able to see all operations recorded for the row you inserted.  My guess is that there is one operation for the Insert that contains the SYS_CHANGE_CONTEXT value and another operation after it where there is no context specified.  If so, hopefully that will help you track down where the other operation is coming from.

    Hope that helps.

    Thanks, Mark

     

    Friday, October 29, 2010 2:00 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi all,

    This problem is so frustrating. I’m even unable to replicate the problem on another database instant in the same version. I don't find any solution up to now.

    Due to some reason, I upgraded to SQL Server 2008 R2.  The ‘WITH CHANGE_TRACKING_CONTEXT’ worked properly (be able to set SYS_CHANGE_CONTEXT on the changetable).

    I can’t say that the upgrade is the answer as the problem didn’t attack the other instant. This problem is still confusing. It is like an anomaly.  

    Agung

    Thursday, October 28, 2010 7:45 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Agung

    The values of CHANGE_TRACKING_CURRENT_VERSION will differ greatly between databases and have nothing to do with the problem.  The "other database" is probably new with few updates and the value is therefore low; the database which reported 5944 will have been around for longer and have had a lot more transactions executed on it.

    The only thing I can think that would cause your problem is that there is a further operation being performed on the same row after the insert and before you query for changes.  Is there a trigger on that table on that database?  Could there be a further update after the insert?  Can you run the profiler to see what is happening?

    Do the above first.  If that does not reveal anything then another thing to try is to dump out the contents of the internal tracking table, which will list all the operations to that row.

    • Login using a dedicated admin connection (see books online for further info)
    • Find the name of the tracking table using sys.internal_tables
    • Select from the internal table for your Address table - you should be able to see all operations recorded for the row you inserted.  My guess is that there is one operation for the Insert that contains the SYS_CHANGE_CONTEXT value and another operation after it where there is no context specified.  If so, hopefully that will help you track down where the other operation is coming from.

    Hope that helps.

    Thanks, Mark

     

    Friday, October 29, 2010 2:00 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Mark,

    I'm in attempt to do your suggestion.  I find no other action after the insert (no trigger, no SQL Agent). 

    Then I find difficulty to select from the internal table. This link says that the statement is not allowed: Internal tables do not contain user-accessible data, and their schema are fixed and unalterable. You cannot reference internal table names in Transact-SQL statements. For example, you cannot execute a statement such as SELECT * FROM <sys.internal_table_name>.

    This error is displayed on running the select statement against the internal table:

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'sys.change_tracking_596913198'

    How to access the internal table?

     

    Thanks,

     

    Agung

     

     

    Friday, October 29, 2010 8:31 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    It looks like you are not executing the select from the internal table using a dedicated admin connection.  Books online has some information, but one way to do this is:

    • Have no connections open in SSMS
    • Select "New Query"
    • The connection dialog should be displayed.  Prefix the server name with "admin:"
    • Ensure the database is selected
    • You should now be able to select from the internal table

    The results will show all tracked operations for the table.  This is the data which is used to create the aggregated results for CHANGETABLE(CHANGES ...).

     

    Friday, October 29, 2010 3:46 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Mark,

    I assumed dedicated admin connected (DAC) as the admin account, my fault. I should open it using "Database Engine Query" (instead of "New Query") and prefixed "admin:" on the server name as you said.

    Having accessed the database using DAC, I got list of the operation. There were some operations after the insertion. The SYS_CHANGE_CONTEXT value was set on the first (insertion) operation. The value on subsequence operations were null. Your thought are correct.

    I'm still confusing which thing caused the problem on my system.  However, it is proven that the WITH CHANGE_TRACKING_CONTEXT did set the SYS_CHANGE_CONTEXT value.

    Great guidance Mark, Thanks

    Agung

    Monday, November 1, 2010 9:23 AM