Thursday, October 22, 2009

Executing many scripts at once in Sql Mangement Studio

I have 110 stored procedures to execute. I can use a tool such as Red Gate's Sql Toolbelt to script the differences, however I'm setting up a new database. I have several choices:
1. create one large script file.
2. script differences
3. run each script in management studio one by one
4. call osql.exe for each and every script
5. load all scripts (drag and drop) into management studio without being prompted every time (quickest method)

scenario 1 - I have to put everything into one hugggeee query file. This will take time to run and execute.

scenario 2 - I can script changes, however this doesn't guarantee we have current changes in our source control system. Quite often changes can get into a database that aren't properly scripted. I'm a fan of this, but it is always good to establish a baseline with your scripts to make sure they are correct (if your environment is source control and script based). The issue is in the database Im about to compare, there are many many other changes that are not required for the application functionality I'm working on.

scenario 3 - last time I tried opening 110 files in management studio, it crashed. Also it prompts for _every_ connection and there is no auto-connect option in the application's interface.

scenario 4 - this requires writing a script, which is generally trivial to enumerate a folder and run osql, however this requires combing through the output for errors and running queries with issues again.


scenario 5 - If I can only get management studio to not prompt me, I can run a script and see the output and continue. By default, management studio will prompt you on each and every query you open. However, if you launch a command line with a list of files, or a single INVALID file, it will retain the connection info, and all new items you drag and drop will automatically be opened with this connection.

so simply run the command:

sqlwb -s ServerName -d DatabaseName -u UserName -p Password -e fakefilename.txt

(im assuming sql authentication here for this example not integrated).
Now drag and drop all of your scripts here and they will open with a connection, and without prompting you.

You will still have to press f5 for every script, but for me it took two minutes to process 110 files pressing f5 to execute each one and clicking the x to close each one after there were no errors.

This saved me a little bit of time : )

2 comments:

  1. As you own the SQL Toolbelt, you could use SQL Compare's 'compare-to-scripts' feature to compare the stored procedure scripts against your DB. This assumes they're held in .sql files. SQL Compare will let you apply the changes directly or generate a single synchronization script that you can run later. Please contact me if you have any trouble getting this to work and I'll be glad to help!

    David Atkinson (David dot Atkinson at Red-Gate.com)

    ReplyDelete
  2. ah yes... we had a similar debate at work. First off.. red gates products rock and I recommend them to just about every company I go to. However in some cases diff scripts wont cut it. You may have several developers working on separate scripts. There may be 'add ons' to the system in different departments and you may only want to script what you _know_ are your changes.. IE what exists in a specific source control branch. In a situation with us, we have to have individual files that another department will use (a build team) to create the environment. Simple diff scripts don't tell us if what we have in our source control environment is correct or not. So.. in our case everything is 'sourced' from source control. However your products have come in handy in our corporate environment to compare after the deployment, and actually at my prior company we used them for deployments - just a different procedure now. thanks!

    ReplyDelete