Temporal Table Errors in DACPAC Deployment

_{Friday, June 5, 2020 9:35 PM}

I have modifications to my database which include:
Add column to Temporal table
Alter Stored Procedures that reference the new column

When I deploy the DACPAC it appropriately turns of System Versioning on the Temporal table.
Makes the change to the History table and the primary table.

Alters the stored procedures

and i get an error during the stored procedure alter saying that the primary table is not a System Versioned table.

The stored procedure uses the FOR SYSTEM_TIME Clause.

The underlying issue seems to be that it did not turn System Versioning back on.

In Visual Studio i generate a compare script between the two databases and it does the same thing.

The system versioning is not turned back on until the end of the script.

Has anyone else had this problem?
Is there a workaround?

_{Monday, June 8, 2020 8:08 AM}

HI Built,

It is better that you could provide Temporal table DDL, insert data query,  stored procedures key codes and detailed error message.

According to the limited information you provided, please find below link for more details may be helpful.

Temporal Tables in Sql Server 2016 Part 4: DDL operations on the System-Versioned Temporal Table

Best regards,

Melissa

MSDN Community Support

Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

_{Monday, June 8, 2020 8:15 AM}

Read this thread

https://stackoverflow.com/questions/49577201/how-to-deploy-temporal-tables-with-dacpac-and-sqlpackage-exe

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

_{Monday, June 8, 2020 1:38 PM}

When the DACPAC executes:

1. It turns off System Versioning for the temporal table
2. The primary and history tables are Altered to add the new column
3. Then the Alter Stored Procedure is executed
4. The Alter Stored procedure fails because the Temporal table is no longer a System Versioned table because System Versioning has been turned off
5. Later in the script is where the System Versioning is turned back on.

It should:

1. Turn off System Versioning
2. Alter the table
3. Turn System Versioning back on
4. Then alter the stored procedure