Order of scripts being run for deployments

Monday

Getting started with SCA and running into an issue:
I needed to alter a function which was used in a check constraint on a table. In order to do this the constraint must be dropped, function altered then the constraint can be added back. SCA is not smart enough to run the changes in the correct order. I figured I would make each change individually one by one and import , generate scripts, refresh , etc 3 separate times. This passed the shadow database verification but on deployment, SCA still does not run in the correct order.

I see there are pre and post deployment folders. Is the correct approach to add scripts to drop and re-add constraints there? If so I don't want these scripts running for all deployments so I would delete them after my deployment? 

Thanks

MikielAgutu

Hello Monday

Sorry you're having issues using SQL Change Automation.

There are a couple of things you could try here. First, instead of importing changes one by one, can you try to manually write a migration script that performs the required operations in the correct order?

If that doesn't work, you may need to alter settings that control the order in which SQL Change Automation deploys scripts.

From the documentation page:

'By default deployment order is determined by file path, taking into account both folder path and file name. The file name must always start with a numeric prefix. To adjust the order of deployment, simply change the file's numeric prefix. If two migrations have the same number, then the rest of the filename will be used as the tie-breaker.'

You can also configure the order of deployment with Migration Grouping, here.

If you're still not having any luck, then Pre & Post-Deployment Scripts might help, as you said.

In order to ensure the Pre/Post scripts are only executed in certain environments you'll have to manually write some SQL to check which DB context you're in. I don't recommend adding Pre/Post scripts, deploying the project, and then deleting them. Such an approach would lead to inconsistency if you source control your project.

Hope that helps

Mikiel

MikielAgutu

Hello Monday

It looks like a script ordering issue to me.

Programmable Objects contain logic to drop and re-create themselves upon deployment. In this case, it looks like both objects have already been deployed to the shadow database.

When you refresh, the project is deployed to the shadow database once more. When Return_RM_ID is deployed, it attempts to drop and re-create. However because a dependency exists between these two objects, the server blocks the change, which is fair enough.

To fix this you'll have to manually amend the Programmable Object file in order to ensure the objects are deployed in the correct order every time. You can see more about how to do this on this documentation post.

Thanks.

MikielAgutu

It's perfectly fine to edit the Programmable Object scripts manually. As long as your manual changes are valid there shouldn't be any issues with clashes between automatic and manual changes.

Monday

Thanks, the part I was missing was how to manually add a migration script. I think this is the direction I should take. After doing this though and clicking refresh I get a failed to verify against shadow database error. The error is on the Programmable Objects not the new migration script I just created. I deployed the migration script with the button in visual studio.

How do I fix this?

***** EXECUTING MIGRATION "Programmable Objects\dbo\Functions\Return_RM_ID.sql", ID: {f5c49548-a0de-5be0-be35-18ab932660b4} *****

Msg 3729, Level 16, State 1, Server xxxxx\xxxxx, Line 2

Cannot DROP FUNCTION 'dbo.Return_RM_ID' because it is being referenced by object 'CHK_RM_ID'.

Monday

The Programmable Object file was automatically generated by the tool. If I manually change it, will my changes get overwritten at some point by the tool again?