SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Executing Multiple Scripts in a Folder using the ScriptRunner Utility

By Andy Warren,

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!

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

Set oFolder = Nothing
End If

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

Set fso = Nothing

Exit Sub

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:


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"
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"
End With
Set x = Nothing
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!

Total article views: 9643 | Views in the last 30 days: 3
Related Articles

SQL Script to find the last executed commands on the SQl Server

Sometimes we needs to find out the last executed commands on the database server.  Below is the SQL...


URGENT: Error executing scripts......

URGENT: Error executing scripts......


DBCC command execution History

When did a DBCC command executed in a DB


rename the filename using script task in ssis

rename the filename using script task in ssis


String Execution in SQL

Execute String in SQL 2005

sql server 7    
stored procedures    
visual basic 6