sequence of script execution

GurpreetGill

Hi

I am using SQL Change Automation in Visual Studio, Facing an issue with sequence of script execution.

Here is what i am doing

1) Add a Stored Procedure ( let’s say addValue) in Source database

2) Add a migration script, which calls the stored Procedure ( addValue)

3. Make sure Shadow Database is generate ( check the check box to generate Shadow database)

4. Build the solution, it is failed, error says Stored Procedure addValue is not available

 I know the issue, as migration scripts are executed before programmable objects. by the time it runs the script ( step 2) stored procedure addValue is not available in the shadow database.

 I already explore the deployment order in the project property, but this is for only programmable objects. May be in my case i need the mix of deployment order for migration script and programmable objects.

 please help.

Thank you

Gill

 

Kendra_Little

Hi @GurpreetGill ,

I might handle this in different ways, depending on how frequently I expect this pattern to happen in the course of my workflow. 

• If this may happen frequently, disabling the programmable object feature for the project and using a "versioned" migrations only approach is likely the best option. If you would like to do this, there are two steps. 1) The "ProgrammableObjectHandling" setting can be set to ScriptInMigrations.  2) You likely would also want to  modify the "SyncToOfflineSchemaModelObjectTypes" setting so that includes programmable objects in the offline schema model.
• If this is a rare occurrence, the simplest option is to split the deployment in two -- deploy the PO first in one deployment and then the migration in the next deployment.
• If this is a rare occurrence and splitting the deployments into two is an issue, one could also leave POs enabled and manually create a migration script with the same PO in it for ordering purposes, but manual validation that these are in sync for that deployment is of course needed.

Essentially, the Programmable Object feature does generally make ordering of changes simpler. But for the scenario you mentioned it actually makes it a bit difficult. For that reason we do support disabling the feature for workflows where it may not be a good fit.

Hope this helps,
Kendra