none
Converting existing table to temporal table and getting error RRS feed

  • Question

  • I have a very simple table with just one column and one row of data. I try to convert it to a temporal table with these commands:

    ALTER TABLE TBD_salesperson
        ADD
            sys_start_time DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN DEFAULT SYSUTCDATETIME()
          , sys_end_time   DATETIME2 GENERATED ALWAYS AS ROW END   HIDDEN DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59')
          , PERIOD FOR SYSTEM_TIME (sys_start_time, sys_end_time);
    GO

    ALTER TABLE TBD_salesperson
        SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TBD_history_salesperson));

    And I get this error:

    Msg 13575, Level 16, State 0, Line 1
    ADD PERIOD FOR SYSTEM_TIME failed because table 'vcc-max.dbo.TBD_salesperson' contains records where end of period is not equal to MAX datetime.

    How can that be? I only have one row in the table and the only column is an id field. I tried both on Azure SQL and with a local db and got the same result.

    Please help.

     

    Friday, February 14, 2020 10:45 PM

All replies

  • I think the issue is with the datetime2 precision, the default is 7, so, you would have to inculde 9999999 milliseconds

    other option is to mention as datetime2(0).

    ALTER TABLE TBD_salesperson
        ADD
            sys_start_time DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN DEFAULT SYSUTCDATETIME()
          , sys_end_time   DATETIME2 GENERATED ALWAYS AS ROW END   HIDDEN DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999')
          , PERIOD FOR SYSTEM_TIME (sys_start_time, sys_end_time);
    GO
    
    ALTER TABLE TBD_salesperson
        SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TBD_history_salesperson));


    Hope it Helps!!

    Saturday, February 15, 2020 7:44 AM
  • Yes, that works !!!! Thank you so much !!! The error message was quite misleading.
    Saturday, February 15, 2020 7:23 PM
  • Hi Alex S Barros,

    Glad to know that your issue has been solved. In order to close this thread, please mark useful reply as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

    Best Regards,

    Amelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 17, 2020 6:55 AM