為現有表啟用系統版本控制時出現錯誤 13542

我正在嘗試為 SQL Server 2016 數據庫中包含數據的某些現有表啟用系統版本控制。我正在按照Microsoft 的這些說明進行操作。

其中一張表如下所示：

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

我嘗試執行的腳本如下所示：

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

執行腳本的結果是以下錯誤：

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.

關於這個錯誤，我找不到太多資訊。在這篇文章的評論中有一個建議，如果 UTC 早於伺服器時間，預設使用 UTC 可能是一個問題，但我在英國的夏令時，比 UTC 早一個小時。我試過使用GETDATE以防萬一，但發生了同樣的錯誤。

我有類似的問題，將 alter table 拆分為兩個單獨的語句解決了它。嘗試

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

只需更改SYSUTCDATETIME()為DATEADD(MINUTE,-1,SYSUTCDATETIME())

引用自：https://dba.stackexchange.com/questions/184601