6

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.

  • 1
    I doubt it will make any difference, but try '9999-12-31T23:59:59' as the date literal. – Max Vernon Aug 29 '17 at 16:00
  • I notice that you appear to be adding the SysStartTime and SysEndTime columns as nullable. This could mean that the data is blank for existing rows. What if you add them as not null, do you get the same issue? EDIT: never mind, appears as though they get created as non null by default. Can you check the data in those columns? I get no errors with your script, and a row of data on 2016 in PT time zon. – Nic Aug 29 '17 at 16:03
  • I've tried adding not null, and also changing the time format, but I'm still getting the same error. I can't check the data in the columns as they're never successfully created. Not sure what the relevance of this is, but I'm doing this on a local database created from an export of an Azure SQL Database - I just tried setting up the same temporal table on a fresh database and it worked fine. – Jonathan Sayce Aug 29 '17 at 18:25
5

I had similar issue and splitting alter table into two separate statements solved it. Try

ALTER TABLE dbo.ClientBeacon ADD 
  datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT F_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');

GO

ALTER TABLE dbo.ClientBeacon ADD 
  PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);

GO
2

Just change SYSUTCDATETIME() to DATEADD(MINUTE,-1,SYSUTCDATETIME())

1

I know this is older post, but I just had a same problem. And this one worked.

CONSTRAINT DF_TableName_SysStart

DEFAULT DATEADD(SECOND,-1, SYSUTCDATETIME()) -- **WORKED**

-- other options I've tried
DEFAULT DATEADD(SECOND,0, SYSUTCDATETIME()) -- didn't work
DEFAULT SYSUTCDATETIME() -- didn't work
DEFAULT SYSDATETIME() -- didn't work
DEFAULT GETDATE() -- didn't work
DEFAULT DATEADD(MINUTE,-59, SYSDATETIME()) -- didn't work
DEFAULT DATEADD(MINUTE,-60, SYSDATETIME()) -- **WORKED**
0

Old post, but I ran into this today and got it working. The culprit is in the resolution of SysStartTime. Use this:

ALTER TABLE dbo.ClientBeacon   
   ADD   
      SysStartTime datetime2(7) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_ClientBeacon_SysStartTime DEFAULT SYSUTCDATETIME(),
      SysEndTime datetime2(7) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_ClientBeacon_SysEndTime DEFAULT CONVERT(datetime2 (7), '9999-12-31 23:59:59.9999999'),   
      PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);   

My (semi-educated) guess would be that SQL Server rounds the time to the nearest second and sometimes it rounds it up. Then it looks at the time and goes "whoa, cowboy, that time is in the future!" and fails.

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.