CODEWITHSUNDEEP
sql-server
Isak
Isak

Reputation: 1621

Updating temporal table history

I'm having a problem updating rows in a temporal history table in MS SQL Server 2016.

From the documentation, the steps that should be needed are as follows:

  1. Disable system versioning
  2. Modify the history table
  3. Enable system versioning again

I tried creating a procedure that does this, but got this error:

Msg 13561, Level 16, State 1, Line 23 Cannot update rows in a temporal history table 'db.dbo.FooHistory'.

Here is my SQL:

CREATE TABLE Foo(
    id int primary key not null
    , title nvarchar(50) not null
    , startTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
    , endTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
    , PERIOD FOR SYSTEM_TIME (startTime, endTime) )

ALTER TABLE Foo
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.FooHistory));

GO

CREATE PROCEDURE [dbo].[UpdateFooHistory] AS
  BEGIN
    SET NOCOUNT ON

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    BEGIN TRANSACTION;
    ALTER TABLE dbo.Foo SET (SYSTEM_VERSIONING = OFF);      

    UPDATE dbo.FooHistory
    SET title = 'Foo';

    ALTER TABLE dbo.Foo SET (SYSTEM_VERSIONING = ON (
      HISTORY_TABLE = dbo.FooHistory,
      DATA_CONSISTENCY_CHECK = ON
    ));

    COMMIT TRANSACTION

    RETURN 0
  END
GO

It seems like SQL Server is checking if a table is temporal at "compile time" rather than at runtime. Is this true? Is there a way to work around it?

Upvotes: 16

Views: 16929

Answers (3)

Dai
Dai

Reputation: 155145

With SQL Server 2022 (and Azure SQL), I see the error is being raised by the query parser before it even attempts to run the SET ( SYSTEM_VERSIONING = OFF ); part.

...and as with other errors raised by the parser before it even runs any DDL, the trick is to add a GO batching instruction so that the parser stops reading ahead and will run with what it read up-to-that point and only run the rest of the stateements when the previous statements complete.

I also had my statements wrapped in a transaction too (though using ISOLATION LEVEL SERIALIZABLE wasn't necessary):

Like so:

SET XACT_ABORT ON;

BEGIN TRANSACTION ddlTxn1;

ALTER TABLE dbo.MyTemporalTable SET ( SYSTEM_VERSIONING = OFF );

/* The `GO` separator below stops SQL Server from complaining about the UPDATE below before it runs the `SET ( SYSTEM_VERSIONING = OFF )` above: */

GO

UPDATE dbo.MyTemporalTable         SET Foo = 'bar';
UPDATE dbo.MyTemporalTable_History SET Foo = 'bar';

ALTER TABLE dbo.MyTemporalTable SET (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE          = dbo.MyTemporalTable_History,
        DATA_CONSISTENCY_CHECK = ON
    )
);

COMMIT TRANSACTION ddlTxn1;

Further reading:

Upvotes: 1

Robert J
Robert J

Reputation: 754

With SQL Server 2019 it does not work any more (neither with exec nor with sp_executesql). You need to use a workaround.

In order to prevent any other user to change something while the versioning is off first start a serializable transaction:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 
BEGIN TRANSACTION;

Temporarily disable SYSTEM_VERSIONING on the table:

ALTER TABLE Foo SET (SYSTEM_VERSIONING = OFF);

Modify the history table:

UPDATE dbo.FooHistory SET title = 'Foo';

Re-enable the versioning:

ALTER TABLE Foo SET (SYSTEM_VERSIONING = ON (  HISTORY_TABLE = dbo.FooHistory, DATA_CONSISTENCY_CHECK = ON));

Commit the transaction:

COMMIT TRANSACTION;

Upvotes: 9

Isak
Isak

Reputation: 1621

Change the UPDATE statement to this, and it will let you create the procedure:

EXEC(N'UPDATE dbo.FooHistory SET title = ''Foo''');

Upvotes: 9

Related Questions