Updating temporal table history

Question

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:

Disable system versioning
Modify the history table
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?

Does this answer your question? [Cannot delete rows from a temporal history table](https://stackoverflow.com/questions/53746197/cannot-delete-rows-from-a-temporal-history-table) — Michael Freidgeim, May 24 '22 at 11:24

score 9 · Accepted Answer · answered Oct 13 '17 at 21:25

9

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

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

answered Oct 13 '17 at 21:25

Isak

1,591
1
17
23

This.. does not work. – Jack0fshad0ws Jan 30 '23 at 22:49
Just tested it again and it still works on what I'm using (2019, RTM-CU18). Maybe it is also affected by database settings, not sure. – Isak Feb 01 '23 at 16:11
1

My local is 2022, and tested in Azure DB.. it didn't work there for me. – Jack0fshad0ws Feb 01 '23 at 18:23

score 6 · Answer 2 · answered Nov 25 '21 at 14:46

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;

Dynamic sql does work for me on Azure Sql db – Michael Freidgeim May 24 '22 at 11:25 — Michael Freidgeim, May 24 '22 at 11:25

score 0 · Answer 3 · answered Jul 16 '23 at 20:24

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;

Updating temporal table history

3 Answers3