I'm trying to enable system-versioning for some existing tables in a SQL Server 2016 database, which contain data. I'm following these instructions from Microsoft.
One of the tables looks like this:
CREATE TABLE [dbo].[ClientBeacon]( [ClientId] [int] NOT NULL, [BeaconId] [int] NOT NULL, [FromDate] [datetime] NOT NULL, [ToDate] [datetime] NULL, [Deleted] [bit] NOT NULL, [ModifiedByUserId] [nvarchar](128) NOT NULL, [ModifiedOn] [datetime] NOT NULL, [Timestamp] [timestamp] NOT NULL, CONSTRAINT [PK_ClientBeacon] PRIMARY KEY CLUSTERED ( [ClientId] ASC, [BeaconId] ASC ) )
And the script I'm trying to run looks like this:
CREATE SCHEMA History; GO ALTER TABLE dbo.ClientBeacon ADD SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_ClientBeacon_SysStartTime DEFAULT SYSUTCDATETIME(), SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_ClientBeacon_SysEndTime DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59'), PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime); GO ALTER TABLE dbo.ClientBeacon SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.ClientBeacon)); GO
The result of running the script is the following error:
Msg 13542, Level 16, State 0, Line 4 ADD PERIOD FOR SYSTEM_TIME on table 'test.dbo.ClientBeacon' failed because there are open records with start of period set to a value in the future. Msg 13510, Level 16, State 1, Line 11 Cannot set SYSTEM_VERSIONING to ON when SYSTEM_TIME period is not defined.
There isn't much I can find about this error. There's a suggestion in a comment on this post that using UTC in the default might be an issue if UTC is ahead of the server time, but I'm in the UK on daylight savings time, an hour ahead of UTC. I've tried using
GETDATE just in case, but the same error occurs.