- 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));
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?