What are the challenges you face when working across database platforms? Take the survey
Options

sqlproj file ordering

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?

Tagged:

Answers

  • Options
    ducmanducman Posts: 26 Bronze 2
    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.
  • Options
    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. 

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our 
    Help Center?

  • Options
    jnikjnik Posts: 4 Bronze 1
    @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?
  • Options
    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

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our 
    Help Center?

Sign In or Register to comment.