Comments
2 comments
-
Hi Bo Ngoh
Sorry, to hear you're experiencing this issue!
I just tested this scenario myself without issue, so I'm wondering do you know what version of SQL Change Automation you're using?
It could also be the case of different comparison options causing trouble.
I'm happy to reach out directly via a support ticket to troubleshoot further if that works for you!
-
SSMS applies changes in dependency order automatically, but SQL Change Automation sequences migrations explicitly, so the FK and data update can run before the Pips NYT parent row exists - requiring a pre-deployment or manual migration to fix the order.
Add comment
Please sign in to leave a comment.
I have a scenario in which these changes happened at the same pull request to Git:
1. Added a new row to static data table ‘A’
2. Added a new column in non-static data table ‘B’, with foreign key to table ‘A’
3. Assigned the value of the new row in ‘A’ to the new column of an existing row in table ‘B’.
When pulling down the changes in another database via SQL Source Control + SSMS, the changes came across smoothly, no issue.
In SQL Change Automation however, the process tried to execute (2 + 3) before (1), resulting in a foreign key error.
The setup, performed on Azure DevOps, was to:
1. (Pipeline) Build an SQL Source Control project and publish the nuget package
2. (Release) Take the nuget package and execute ‘Deploy Database Changes from a Build artifact’
The situation is common enough that I believe it happens all the time without incident but this time it's tripped us up and we can't figure out why. When we tried pre-adding the foreign key to target database before re-running the deployment, this passed correctly. Was the FK being new something that tripped up the sequence? Why does this not happen in SSMS's implementation then?