Advertisements
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


Advertisements
  1. 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;

  2. December 14, 2016 at 6:01 pm

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

  3. 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

  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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: