Doug Bishop (11/29/2010)
I am still confused as to how using scripts is better than using stored procedures and using a job with multiple steps to control the flow. Furthermore, in Sadequl's article, he specifically mentions that you cannot check for errors that occur between scripts, something that can be done with a job and you are controlling the flow of execution of the steps. You could add that, but then your code to execute code starts becoming complex, using pipes and FIND and branching (mind you extremely limited branching). Using a job, each step can branch to a completely different step if the executing step fails, thus customizing error handling.
For software releases, especially if they are to multiple servers or third party systems you need a rubust and reliable method to deploy your scripts. A job is just not going to do this, and there is always the question of creating the job in the first place. There are means to check a script has worked so I am not sure where in Sadequl's article it says otherwise or if it's been taken in the correct context.
Doug Bishop
I am also somewhat opposed to saying, "execute anything in the folder and always assume the last character of the filename (prior to the period) is the sort order". The code presented would attempt to execute a temp file, a text file, a backup copy of a current script, etc. At the very least, if I was going to implement this methodology, I would have a control table in SQL Server that specified the script name and order of execution. I could then pull the script name from that table in execution order. Then I could give my scripts meaningful names without worrying about them having the "correct" prefix or suffix.
If you control what is in the given folder and the names of the files, then it is not a problem. How do you get the data into the control tables that you mention?
Doug Bishop
I did not state, or even imply that backups are a method of version control. I stated that you can either integrate your version control software into SQL Server, or script the procedure and manually add updates to your version control (check out existing, check in changes). I was implying that backing up the database backs up the procedures.
You can integrate version control with SSMS, that is a tool not SQL Server itself and Visual Studio or some third party tools can also interface with version control systems. Version control is only the starting point for a release process and a small part of implementing change control. Third party tools can package up releases for you, but not everyone wants to use them or has the budgets to buy them.
Martin