Post reply

SQL server change table query not working as expected

  • February 14, 2020 at 1:33 pm

    #3725915

    Hi,

    It looks like that a CHANGETABLE query for a particular table is not working correctly.

    I created a record in the bill_details table and then updated it.

    The record was created with sys_change_version=5000.

    The records was updated with sys_change_version=5001.

    But the following CHANGETABLE queries are confusing me and apparently returning incorrect sys_change_creation_version:

    select * from CHANGETABLE(CHANGES bill_details, 5000) ct  order by bill_id;


    sys_change_version       sys_change_creation_version        sys_change_operation       sys_change_columns     sys_change_context     bill_id
    -------------------      -----------------------------      ----------------------     -------------------    --------------------   --------
    5001                     5001                                U                          NULL                   NULL                   2018


    select * from CHANGETABLE(CHANGES bill_details, 4999) ct  order by bill_id;


    sys_change_version       sys_change_creation_version        sys_change_operation       sys_change_columns     sys_change_context     bill_id
    -------------------      -----------------------------      ----------------------     -------------------    --------------------   --------
    5001                     5001                                I                          NULL                   NULL                   2018

    I would expect that if the sys_change_operation is 'U' then the sys_change_creation_version would be NULL.

    And if a record is updated and if I am searching using a smaller 'from version' number then the sys_change_version and sys_change_creation_versions would be different.

    Any ideas what I am missing? Or why these CHANGETABLE queries are behaving in an unexpected manner?

    Regards.

  • February 15, 2020 at 2:10 pm

    #3726183

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • February 20, 2020 at 11:00 am

    This was removed by the editor as SPAM

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply