DevOps Post-update schema failure with Temporal Table
RichUchytil
Posts: 6 New member
SQL Server 2016, Azure DevOps
At the top of the logs:
I'm trying to add a new temporal table. It is not in the target server. I have run the code in SSMS and it creates the temporal table just fine so I know the code works. When I deploy the code I get the error, "Post-update schema check failed: the schema in the Database Release object and database on server are different, or static data is different."
I have had others look at my temporal table code and they all say it is correct yet no matter what we do it keeps giving us this error. It works fine with regular tables and everything else, just throws this error with this temporal table. I have created other temporal tables and had problems too. In one case I had a create for the regular table and the history table in the same sql. I removed the history create and it worked. In another case I had to delete the table from the repository, deploy it to drop the table, then deploy the temporal table. This time nothing is working.
Why does this have problems with temporal tables and what is the fix? Is there a specific syntax I need to follow?
At the top of the logs:
2021-07-08T21:51:18.2089160Z ==============================================================================
2021-07-08T21:51:18.2089460Z Task : Redgate SQL Change Automation: Release
2021-07-08T21:51:18.2089713Z Description : Safely deploy database changes with Redgate SQL Change Automation
2021-07-08T21:51:18.2090123Z Version : 4.2.3
2021-07-08T21:51:18.2090277Z Author : Redgate Software
2021-07-08T21:51:18.2091379Z Help : [More Information](https://www.red-gate.com/sca/ps/help)
2021-07-08T21:51:18.2091655Z ==============================================================================
I'm trying to add a new temporal table. It is not in the target server. I have run the code in SSMS and it creates the temporal table just fine so I know the code works. When I deploy the code I get the error, "Post-update schema check failed: the schema in the Database Release object and database on server are different, or static data is different."
I have had others look at my temporal table code and they all say it is correct yet no matter what we do it keeps giving us this error. It works fine with regular tables and everything else, just throws this error with this temporal table. I have created other temporal tables and had problems too. In one case I had a create for the regular table and the history table in the same sql. I removed the history create and it worked. In another case I had to delete the table from the repository, deploy it to drop the table, then deploy the temporal table. This time nothing is working.
Why does this have problems with temporal tables and what is the fix? Is there a specific syntax I need to follow?
Tagged:
Best Answer
-
RichUchytil
Posts: 6 New member
Found the problem. I had COLLATION set for two of the columns in the table definition. Removed that and it deployed just fine. Originally I didn't have them and didn't have NULL/NOT NULL on every column. When it failed and I looked at the logs I saw NULL/NOT NULL on the columns and the collation stuff. So I added all of that to my code thinking the compare was looking for that stuff. Turns out I should have only added the NULL/NOT NULL stuff.
It would be nice if the error message could give something more helpful to point to the issue instead of basically, "Sorry, it failed, go figure out why." At least now I know what to look for if it happens again.
