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 : )
