DACPAC complaining about views with FOR SYSTEM_TIME Clause

2
votes

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://docs.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!

1
sql-serverdacpactemporal-tables
Do you have a temporal table? - Alex
Are you using the latest version: docs.microsoft.com/en-us/sql/tools/… ? - ErikEJ
I am downloading the latest version today to find out if it works better. Thanks - Daisy
Yes , all tables in the database are system-versioned, except views of course. - Daisy
Did you end up finding a solution to this? - Nick Darvey

1 Answers

1
votes

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.