CODEWITHSUNDEEP
sql-serverdacpactemporal-tables
Daisy
Daisy

Reputation: 177

DACPAC complaining about views with FOR SYSTEM_TIME Clause

We use SSDT DACPAC to deploy our database project. We receive this error when publishing

Error SQL72014: .Net SqlClient Data Provider: Msg 13544, Level 16, State 1, Procedure sp_refreshsqlmodule_internal, Line 85 Temporal FOR SYSTEM_TIME clause can only be used with system-versioned tables. 'reporting.dbo.VW_xxxxxxx' is not a system-versioned table. Error SQL72045: Script execution error.

The view can be created and altered in SSMS without a complaint.
We encapsulate some logic in the view, hence we still would like to use FOR SYSTEM_TIME with the view. How to get DACPAC to work with such objects and clauses?

https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver15 shows a view with FOR SYSTEM_TIME clause.

Thank you!

Upvotes: 2

Views: 1044

Answers (1)

Bill Turner
Bill Turner

Reputation: 1010

We ran into the same problem with an Azure DevOps database deploy - the process appears to drop system versioning to update the tables, then refreshes the views, then adds system versioning back on the modified table.

Our database is built from a Visual Studio 2019 database project. We modified the view's SQL file to not be part of the build (Properties -> Build Action: None) and ran it from a post-deployment script.

Since this could deploy to a fresh database as well as updating an existing database, we had to modify the view to DROP IF EXISTS before CREATE.

Upvotes: 2

Related Questions