Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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!

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!

Total article views: 9584 | Views in the last 30 days: 4
 
Related Articles
FORUM

URGENT: Error executing scripts......

URGENT: Error executing scripts......

FORUM

DBCC command execution History

When did a DBCC command executed in a DB

FORUM

rename the filename using script task in ssis

rename the filename using script task in ssis

FORUM

String Execution in SQL

Execute String in SQL 2005

FORUM

Scripting help - Need a query to find filenames and filepaths under a certain directory

Scripting help - Need a query to find filenames and filepaths under a certain directory

Tags
administration    
miscellaneous    
programming    
sql server 7    
sql-dmo    
stored procedures    
strategies    
t-sql    
visual basic 6    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones