Script execution order

All we need is an easy explanation of the problem, so here it is.

I did Generate Script on a database and got over 1700 scripts. I’ve sorted them into folders below:

Script execution order

Each folder contains .sql files of that type, for example 06 UserDefinedFunction contains many .UserDefinedFunction.sql files.

I’m going to make a batch script that goes to each folder and runs sqlcmd on every file in that folder.

Does the execution order matter here, and do I have the right order?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The execution order absolutely matters, and you shouldn’t change it from what was generated. It is likely you have dependencies in one script on objects that need to be created first from another script. For example, if one of your Stored Procedures use a View then it will fail upon generation because it can’t find the View it references. This is why generating scripts puts them in order by dependency instead of object type.

Only in the very rare case where all your scripts don’t have dependencies on each other (or your re-ordering is coincendentally in order of dependency still), then you’d be ok.

Method 2

It’s better to use a dacpac to do the same thing in a proper way.

https://docs.microsoft.com/en-us/sql/relational-databases/data-tier-applications/data-tier-applications?view=sql-server-ver15

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply