Can SCA deploy always Views before Stored Procedures?

dbuenosilva

Hi there,

We often have developers creating stored procedures that depend on views being created at the same task.

SCA automatically creates the stored procedures in the "Programmable objects" -> <schema> -> "Stored Procedures" folder and views in the "Programmable objects" -> <schema> -> "Views" folder.

As a result, it tries to deploy the stored procedure first, then the views.





To avoid the error, I have to edit the SCA project and change the Deployment Order manually using Visual Studio. It is timing consuming since we have to drag view by view...

Is there a way to define this deployment order defined?

I am unsure if it is possible to rename the directories "Stored Procedures" and "Views". Following these names it would work:
1-View
2-Stored Procedures


Regards,

Diego
 

Peter_Laws

Hello dbuenosilva,

Thanks for your question Diego, are you looking to set the definition once and then have that order respected?

If so, this solution should work for you, it may be worth checking that EvaluateExecutionOrderOnImport is correctly set so your changes aren't overwritten.
https://documentation.red-gate.com/sca/developing-databases/working-with-the-visual-studio-extension/advanced-scenarios-for-visual-studio/customizing-programmable-object-deployment-order-in-visual-studio

Or were you looking to uniformly alter the deployment order by type, so views would be deployed first, then sprocs?

dbuenosilva

Thanks, @Peter_Laws for replying to it.

We use EvaluateExecutionOrderOnImport = FALSE.

We're looking to uniformly alter the deployment order by type so that views would be deployed first, then sprocs. By doing so, we wouldn't need to customise object deployment orders in Visual Studio.

Peter_Laws

Thank you for clarifying, I'm afraid this functionality isn't explicitly available in SQL Change Automation, however you might be able to get closer to what you want.

By editing the programmable objects grouping, you could sort them by object type rather than schema (the default), could that htne make the deployment ordering definition easier?

https://documentation.red-gate.com/sca/developing-databases/concepts/migrations/programmable-objects
referencing https://documentation.red-gate.com/sca/reference/sql-change-automation-project-settings