Executing Multiple Scripts in a Folder using the ScriptRunner Utility

Recently I had a request from reader Mindy Curnutt asking for a way to run a batch of scripts that were in a specified folder, possibly with a specified extension. That's really a pretty interesting idea - one that a lot of DBA's probably come across from time to time. So the question is - what's the best way to do that?
I'm going to cover the couple ideas I had and let you see the solution I ended
up with.

Before I write anything, I like to see if there is anything MS has provided that will do the job for me. Query Analyzer is the obvious tool, but it only lets you load one script at a time - from the graphical interface. If you take a look at BOL, you'll see it also supports a command line interface:

Now maybe I'm just having a bad day, but I couldn't get it to do both tasks. So either I need all the scripts in one input file, or I'll have to be content with loading them quickly, then executing one after the other. If you know how to get it to do both - speak up! The OSQL utility has similar limitations. Considering that both ISQLW and OSQL do support a command line, it should be possible to come up with a way to build a batch file consisting of multiple calls to ISQLW, then execute
it, or you could build the command line in an app and then shell ISQWL. But hey,
those are both UGLY hacks!

I'm not convinced there isn't an easier way, but I know I can solve the problem with some old-fashioned code. SQL-DMO gives you various Execute methods for running scripts, the FileSystemObject is the easiest way to work with the file system. Before I build, let's run through what we need for features. Even though this is a utility, a little extra time now might pay off later!

Nice to Have:1) Log the results
2) Ability to schedule and run as a job. Either using command line parameters like ISQLW does, or some type of config file/table, or ...?
3) Have a pick list of server names and database names.

I decided (based on time available and how I thought I'd be using it), to implement both features, but only item #2
and #3 from the nice to have list. Here is what the interface looks like:

A link to the compiled program and the source code is at the end of the article. I'll discuss a couple key points here. The main code we need is something to find and read the files, and then execute them. I split this up into two different subs, as follows:

If fso.FolderExists(FolderName) Then
'this gives us all the files in the folder
Set oFolder = fso.GetFolder(FolderName)

For Each oFile In oFolder.Files
'user may have provided a filter to only run some of the available scripts
If UCase$(oFile.Name) Like UCase$(FileMask) Or FileMask = "" Then
Call ExecuteSingleScript(oFolder.Path & oFile.Name)
End If
Next

To have the ability to run this app on a scheduled basis, I wanted something simple. If you've tried to parse command lines,
you know that doing it well is not trivial! The config file idea has merit, something you could easily modify using notepad that probably be similar to an ini file, like this:

ServerName="local"
DBName="pubs"
Folder="C:\"
FileMask="*.SQL"

My other idea (and the one I implemented) was to take all the functionality and compile into my favorite thing - an OBJECT! Here is an example of how to use the finished cScriptRunner object in a SQL job:

Now for comparison, take a look at what I do when the user clicks the 'Run Scripts' button in the utility:

If txtServerName.Text <> "" And txtDBName.Text <> "" And txtFolder.Text <> "" Then
Set x = New cScriptRunner
With x
.Connect txtServerName.Text, txtDBName.Text, txtLogin.Text, txtPassword.Text
.ExecuteAllScripts txtFolder.Text, "*.SQL"
.Disconnect
End With
Set x = Nothing
Else
MsgBox "Not enough information supplied to run scripts."
End If

Is that cool or what? No parsing, no config files, code is separated from the interface. Now if I wanted to go back and work on the
remaining nice to have logging the results, how would I proceed? That's a tough call.
It could go in the interface or the object, but I'd say that it should probably go in the object, since regardless of how you run the object you might want the option to log the results.

I know that was a pretty quick trip through a lot of code. If you're new to DMO, I've got several other articles posted here
that will give you a good introduction and hopefully some good ideas as well:

Use this link to download the
source code for the scriptrunnerobject.dll and the compiled SQLScriptRunner
utility. I used VB6 with SP4,
these are compiled with a reference set to the SQL 2000 DMO, but you can change
it to SQL 7 and recompile with no code changes. If you're going to use as is,
you will need to register the dll on your system by running 'regsvr32
scriptrunnerobject.dll'.

Warning - use the executables and/or the source code at your own risk!
This app gives you the ability to run a LOT of scripts very quickly - make sure
you know what you're running and that you're running it on the right server and
database!

If you've got a question or comment, click the "Discuss this article" tab below!