R
rajeshg
Guest
We have Visual Studio 2017 Database project with temporal tables. The deployment with dacpac fails in release pipeline with following error
[_tableX] are blocked by [dbo].[_tableXHistory]'s dependency in the target database.\r\nError SQL72031: This deploymen t may encounter errors during execution because changes to [dbo].[_tableX] are blocked by [dbo].[_tableX]'s dependency in the target database. Microsoft.PowerShell.Commands.WriteErrorException: Deployment on one or more machines failed. System.Exception: Publishing to database 'OMAC' on server 'localhost'.
If put all temporal table changes in post deployment script we get error “Rows were detected. The schema update is terminating because data loss might occur.” That is because on a subsequent deployment, when it doesn’t see the ValidFrom, ValidTo temporal table time columns in create table script, it tries to drop those columns which results in above error.
For a workaround, we had to explicitly create history table. However this will become a maintenance issue going forward since we have several temporal tables and we do not want to do DML changes in both base and history table.
Any suggestions or any additional info is highly appreciated.
Continue reading...
[_tableX] are blocked by [dbo].[_tableXHistory]'s dependency in the target database.\r\nError SQL72031: This deploymen t may encounter errors during execution because changes to [dbo].[_tableX] are blocked by [dbo].[_tableX]'s dependency in the target database. Microsoft.PowerShell.Commands.WriteErrorException: Deployment on one or more machines failed. System.Exception: Publishing to database 'OMAC' on server 'localhost'.
If put all temporal table changes in post deployment script we get error “Rows were detected. The schema update is terminating because data loss might occur.” That is because on a subsequent deployment, when it doesn’t see the ValidFrom, ValidTo temporal table time columns in create table script, it tries to drop those columns which results in above error.
For a workaround, we had to explicitly create history table. However this will become a maintenance issue going forward since we have several temporal tables and we do not want to do DML changes in both base and history table.
Any suggestions or any additional info is highly appreciated.
Continue reading...