sqlproj file ordering

jnik

We have something happening where SCA is deploying programmable objects at the end of the execution instead of the order that is supplied in the .sqlproj file.

It appears that if a Programmable Object, such as a stored procedure is created.

And then a migration script is created shortly therafter with dependency on that programmable object...

When Deployment of the release artifact occurs on a DB that hasn't had recent updates applied, then the migration script will not execute the sproc because the sproc is being deployed at the same time and the programmable objects get deployed after everything else.

This is the case even if the .sqlproj SCA file has the scripts in the appropriate order.

How can we fix that?

ducman

We include the creation of the stored procedure at the top of the migration script or a separate migration script prior to the one that calls the stored procedure (and also leave the stored procedure script in the programmable object as-is).

Note that programmable objects always run at the end.

DanC

Hi @jnik

Unless you've changed the default behavior you will be experiencing this due to the way SQL Change Automation handles Programmable Objects with "UseRepeatableScriptsForAllObjects " which makes it so all PO's are deployed at the end of the deployment, whereas if you change this behavior by using "ScriptInMigrations " then the PO's will deployed at the same time and the dependencies should be respected.

If you change this behavior you will need to delete the Programmable Objects folder and then re-generate the migrations for all your PO's such as your Stored Procedures. 

jnik

@DanC Will SCA automatically know which objects to create migrations for?  Such as stored procedures, functions, etc?  Or will we have to manually make changes to each one?

DanC

Hi @jnik

If you make any changes to any objects such as stored procedures, functions, they will appear in the create migrations tab as normal