7

I have:

  • table with existing data
  • SQL Server 2016 SP1
  • SQL Server Management Studio 17.5

I am using the following statement to make a make my table a temporal one:

ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]
ADD [SysStartTime] DATETIME2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_AnalysisCustomRollupsV2JoinGroups_SysStart DEFAULT GETUTCDATE()  
   ,[SysEndTime] DATETIME2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_AnalysisCustomRollupsV2JoinGroups_SysEnd DEFAULT CONVERT(DATETIME2(0), '9999-12-31 23:59:59'),   
PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime]); 

ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]   
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.AnalysisCustomRollupsV2JoinGroupsChanges));

The issue:

On my local SQL instance I have many databases; it's very strange that the query runs successfully on some of them, and on some of them it gives me the following error:

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

Sometimes, when I am debugging/executing the query, the initial query runs successfully.

I read, that this could be because I have existing data in the table. So, I have change the logic like this:

  • create buffer table and populated it with all records
  • delete the records from the original table
  • create the temporal table
  • move the records back and drop the buffer table

and again, on some databases it is OK, and on other is not. Trying to resolve the issue I have found the following:

For the StartDate I’ve specified the current UTC date – this could be any date and time that’s not in the future, though note it should be a UTC one. If I’d tried to use GETDATE, as I’m currently on British Summer Time, I would get the following error: Msg 13542, Level 16, State 0, Line 51 ADD PERIOD FOR SYSTEM_TIME on table ‘TestAudit.dbo.SomeData’ failed because there are open records with start of period set to a value in the future.

What does the above means? I need to change the machine time? Or because my local machine is not on UTC time I am getting this error sometimes?

  • Did you see this post? blogs.msdn.microsoft.com/vikas_rana/2016/11/29/… – SqlWorldWide Mar 13 '18 at 11:15
  • Yes, check the comments, also. I have find such workaround in this forum, too, but it changed nothing. I need to understand how to change the time settings in order to get this work. Also, I am having the issue even if the table is empty. – gotqn Mar 13 '18 at 12:15
3

I think I have found how to fix my issue, but I am not going to accept this as answer as I am not able to explain what is causing the problem and guarantee this will work anytime. It's fix found after a lot of testing and I will be glad if someone can bring more light here.


I have never used datetime2 with precision. So, I went back to the source of this format datetime2(0) - Alter Non-Temporal Table to be System-Versioned Temporal Table. The only difference with the script I have been using was the date time function. I used GETUTCDATE() as I do not need to be so precised with datetime(0)(2018-03-15 07:21:02 for example) and in the example it is SYSUTCDATETIME(). So, I have changed it.

I have create a script which is dropping a database if exists, restoring a database from backup and then executing my code in a loop (as I said I get the error sometimes and it was very hard to reproduce it).

I have run a the script a lot of times and I was getting different number of fails (sometimes 70%, sometimes 50%, sometimes below):

enter image description here

I have found this Why is GETUTCDATE earlier than SYSDATETIMEOFFSET? discussion about differences between old and new date time functions. Then build the following query:

DECLARE @UTC DATETIME2(0) = GETUTCDATE();
DECLARE @SYSUTC DATETIME2(0) = SYSUTCDATETIME();

WHILE DATEPART(SECOND, @UTC) = DATEPART(SECOND, @SYSUTC)
BEGIN;
    SET @UTC  = GETUTCDATE();
    SET @SYSUTC  = SYSUTCDATETIME();
END;

SELECT @UTC AS [UTC]
      ,@SYSUTC AS [SYS UTC]
      ,DATEPART(SECOND, @UTC) AS [UTC sec]
      ,DATEPART(SECOND, @SYSUTC) AS [SYS UTC sec]
      ,CASE WHEN @UTC < @SYSUTC THEN 1 ELSE 0 END AS [TimeTravelPossible]
      ,CONVERT(DATETIME2(0), @UTC) AS [UTC date]
      ,CONVERT(DATETIME2(0), @SYSUTC) AS [SYS UTC date]
      ,IIF(CONVERT(DATETIME2(0), @UTC) = CONVERT(DATETIME2(0), @SYSUTC), 1, 0) AS [Are The Same];

I just wanted to check if I can get different datetime2(0) dates using sys and not sys date time function. And of course it is possible.

enter image description here

Maybe, the checks that the engine is making is doing something like this, comparing its current date with my newer date and this is causing the error - open records with start of period set to a value in the future.

I have change the script like this and executed 1 000 times last night - no errors were generated. So, I believe I have fixed this particular issue, but I can't be sure.

ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]
ADD [SysStartTime] DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_AnalysisCustomRollupsV2JoinGroups_SysStart DEFAULT SYSUTCDATETIME()  
   ,[SysEndTime] DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_AnalysisCustomRollupsV2JoinGroups_SysEnd DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),   
PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime]); 

ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]   
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.AnalysisCustomRollupsV2JoinGroupsChanges));
  • 1
    I had a strange encounter with this also getting the same error as you. I tried hacking around the issue by doing each component of the alter one piece at a time, but the "PERIOD FOR SYSTEM_TIME" thing is very particular. I somehow got it to work after adding my start/end columns, then dropping them, then on a last ditch effort running the whole statement again - this worked. Just be aware that this is a known MS bug – dyslexicanaboko Nov 29 '18 at 4:38
  • @gotqn Thanks for solution – FindOutIslamNow Jul 30 at 9:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.