Home > SQL Server 2016 > Resolving issues while enabling Temporal Data on an existing Table

Resolving issues while enabling Temporal Data on an existing Table


In my [previous post] about Temporal Data I showed how to setup Temporal on an existing table. That was a simple scenario where the tables does not have historical data, and we added the 2 Period columns on that table, and we didn’t face any issues.
 

–> Check demo to setup Temporal Tables:


 

–> Resolving potential issues while enabling Temporal Data on an existing Table:

There might be other scenarios where an existing table already contains the Period columns and you want to assign them as the Temporal Period Columns. Here, in this post I’ve tried to do the same with an existing table and ran into some issues while setting it up. Resolved the issues in process to make that table a Temporal Table, which we will see below:
 

–> My existing table looks like this, EffectiveStartDate & EffectiveEndDate datetime type columns as Period Columns:

CREATE TABLE dbo.ResourceTD (
	 ResourceID
	 -- other columns here
	,EffectiveStartDate datetime,
	,EffectiveEndDate datetime,
)

 

–> 1. First Issue: While setting up Parent Temporal Table with the ALTER TABLE statement:

ALTER TABLE dbo.ResourceTD 
ADD PERIOD FOR SYSTEM_TIME (
    EffectiveStartDate, 
    EffectiveEndDate
)
GO

… I got following error below:

Msg 13575, Level 16, State 0, Line 42
ADD PERIOD FOR SYSTEM_TIME failed because table ‘SQL2016POC.dbo.ResourceTD’ contains records where end of period is not equal to MAX datetime.

This was evident by the error message, as the columns datatype was datetime initially and I ALTERed them to datetime2 but the values set were not MAX, but like this “9999-12-31 00:00:00”. So, you need to set them to the MAX possible date, like “9999-12-31 23:59:59.9999999” as shown below:

UPDATE dbo.ResourceTD 
SET EffectiveEndDate = '9999-12-31 23:59:59.9999999'
GO

 

–> 2. Second Issue: Finally, while enabling the Temporal Table with History Table:

-- Enabling the System-Versioning ON
ALTER TABLE dbo.ResourceTD 
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ResourceTDHistory))
GO

… I got following error below:

Msg 13541, Level 16, State 0, Line 85
Setting SYSTEM_VERSIONING to ON failed because history table ‘SQL2016POC.dbo.ResourceTDHistory’ contains invalid records with end of period set before start.

I found that the EffectiveStartDate & EffectiveEndDate columns values were not correct, actually EffectiveStartDate was greater than EffectiveEndDate. That might be due to some previous processing of business logic and manual error. So, I swapped the value for those records.
 

–> You will need to check for all possible scenarios until the Historical Table gets created successfully, some as follows (but not limited to these):


-- Scenario #1
SELECT * FROM ResourceTDHistory WHERE EffectiveStartDate IS NULL
SELECT * FROM ResourceTDHistory WHERE EffectiveEndDate IS NULL

-- Scenario #2
SELECT * 
FROM ResourceTDHistory 
WHERE EffectiveStartDate > EffectiveEndDate

 

I faced these two issues related to this single table. You might face similar or other issues with other tables, based upon different scenarios.

I will update this post if I see any other issues while setting up Temporal tables. Or if you come across, please let me know by your comments, Thanks !!!
 

Check & Like my FB Page


  1. iano
    July 14, 2016 at 5:00 pm

    Thanks, helped my out a lot.
    I found you will need to set as the end date to 9999-12-31 23:59:59.9999999 for DATETIME2

  2. December 14, 2016 at 6:01 pm

    Great article ! Was stuck on the datetime2 issue 🙂 Thanks !

  3. Alla
    January 20, 2017 at 11:31 pm

    https://blogs.msdn.microsoft.com/vikas_rana/2016/11/29/msg-13575-or-msg-13597-error-message-while-adding-period-columns/

    ALTER TABLE [CUSTOMERINFO]

    ADD

    SysStartTime datetime2 NOT NULL DEFAULT GETUTCDATE(),

    SysEndTime datetime2 NOT NULL DEFAULT CONVERT(DATETIME2, ‘9999-12-31 23:59:59.99999999’);

    GO

    ALTER TABLE [CUSTOMERINFO]

    ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);

    GO

    ALTER TABLE [CUSTOMERINFO]

    alter column SysStartTime ADD HIDDEN;

    GO

    ALTER TABLE [CUSTOMERINFO]

    alter column SysEndTime ADD HIDDEN;

  4. Swarn
    March 2, 2021 at 5:29 am

    Good article Manoj.
    I have an issue on overlapping “Msg 13573, Level 16, State 0, Line 2
    Setting SYSTEM_VERSIONING to ON failed because history table ‘dbo.history table name’ contains overlapping records”

    Are you able to help?

    • March 3, 2021 at 9:38 pm

      Hi Swarn, I think your History table is having duplicate rows, or records with overlapping dates for different versions of a particular record. You can try finding it by querying the History table. Also do try checking if StartDate > EndDate for any version.

      • Swarn Singh
        March 6, 2021 at 6:18 am

        Thanks for reply Manoj, I found them and resolved them by re-entered them.
        However, I am still after how can I resolve them without reenter history table records may actual data is not available.
        I am dealing with DB2 migration to SQL first time in fact first time dealing with DB2 🙂

        This is self learning for me so any kind help advice appreciate from any where from professional like you.

        So would be great if any advice or solution on how to resolve overlapping issue without enter the history data.

        Best Regards
        Swarn Singh

  5. April 16, 2021 at 4:33 pm

    Hi Manoj

    I have facing below error.. couldn’t find much on net regarding the error. Are you able to help with?

    Msg 13542, Level 16, State 0
    ADD PERIOD FOR SYSTEM_TIME on table ” failed because there are open records with start of period set to a value in the future.

    • April 16, 2021 at 4:44 pm

      Are you setting any future date or timestamp on start date column? or are you using diff timezone date like UTC?

      • Swarn Singh
        April 17, 2021 at 3:20 pm

        it is sorted Manoj by using GETUTCDATE() as default for period column Startdate…. Thanks for reply.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: