SQLServerCentral Article

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:

[-S server_name[\instance_name]]

[-d database] 

[-E] [-U user] [-P password]

[{-i input_file} {-o output_file} [-F {U|A|O}]]

[-f file_list]

[-C configuration_file]

[-D scripts_directory] 

[-T template_directory]

And would you believe it, BOL even gives the following example of how to load scripts using a wildcard:

isqlw -d pubs -E -f "c:\Program Files\Microsoft SQL Server\MSSQL\Install\*.sql"

And another example of how to load and execute a script:

isqlw -S MyServer -d pubs -U sa -P -i input_file -o output_file

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!

Features:

1) Specify a folder, and maybe a file spec (Mindy's suggestion).

2) Specify the server name, database name, and user login/password, or a trusted connection.

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:

Public Sub ExecuteAllScripts(FolderName As String, Optional FileMask As String = "*.SQL")

Dim fso As Scripting.FileSystemObject

Dim oFolder As Scripting.Folder

Dim oFile As Scripting.File

On Error Resume Next

Set fso = New Scripting.FileSystemObject

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

Set oFolder = Nothing

End If

'cleanup

Set fso = Nothing

End Sub

Public Sub ExecuteSingleScript(FileName As String)

Dim fso As Scripting.FileSystemObject

Dim oTStream As Scripting.TextStream

Dim sScript As String

Dim oFile As Scripting.File

Dim sMsg As String

On Error GoTo Handler

Set fso = New Scripting.FileSystemObject

If fso.FileExists(FileName) Then

'if file exists, read it into a string

Set oFile = fso.GetFile(FileName)

Set oTStream = oFile.OpenAsTextStream(ForReading)

sScript = oTStream.ReadAll

Set oTStream = Nothing

'then run it

oDB.ExecuteWithResultsAndMessages sScript, , sMsg

'let the user know

RaiseEvent ScriptResults(FileName, sMsg)

End If

Cleanup:

Set fso = Nothing

Exit Sub

Handler:

RaiseEvent ScriptResults(FileName, "ERROR: " & Err.Number & " in cScriptRunner:ExecuteSingleScript " & Err.Description)

Resume Next

End Sub

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:

dim x

set x=CreateObject("cScriptRunner")

x.Connect "Local","Pubs","sa",""

x.ExecuteAllScripts txtFolder.Text, "*.SQL"

x.Disconnect

set x=nothing

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!

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating