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:
- Introduction
to SQL-DMO
- Using
DMO to Execute a Job
- More
Ideas About Using SQL-DMO
- Using
DMO to Enable & Disable Triggers
- Using
DMO to Restore a Database
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!
