oSQL - How do I run all .SQL files in a folder by running one batch

Hello. I run into many instances where I have 50+ .sql scripts that I need
to run on multiple SQL installs. I want to automate this process some by
being able to drop all my .sql scripts into a folder and then click on my
batch file that will run all scripts in that folder, and create a log file
with the results.
For Example, my Folder has:
01 Sproc1.sql
02 Sproc2.sql
03 Sproc3.sql
etc...
The batch should be able to go through each file and run them one by one.
I know I can 'pipe' all scripts into one file and just run that one file,
but I've run into some issues with that method.
Any help is greatly appreciated!
Thanks,
Tom
I would use "xp_cmdshell" to get the name of all the SQL scripts into a
temporary table using the DOS 'dir /b <your directory>' command, then
process through each file in the temporary table one file at time and again
use "xp_cmdshell" to issue an "OSQL -E -S<yourserver> -i <your file> -o
<outputfile>" for each file. Hope this is enough to give you clues on how
to do this.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Tom" <none@.none.com> wrote in message
news:ublnUExUEHA.2988@.TK2MSFTNGP10.phx.gbl...
> Hello. I run into many instances where I have 50+ .sql scripts that I
need
> to run on multiple SQL installs. I want to automate this process some by
> being able to drop all my .sql scripts into a folder and then click on my
> batch file that will run all scripts in that folder, and create a log file
> with the results.
>
> For Example, my Folder has:
> 01 Sproc1.sql
> 02 Sproc2.sql
> 03 Sproc3.sql
> etc...
> The batch should be able to go through each file and run them one by one.
>
> I know I can 'pipe' all scripts into one file and just run that one file,
> but I've run into some issues with that method.
> Any help is greatly appreciated!
> Thanks,
>
> Tom
>
|||> I want to automate this process some by
> being able to drop all my .sql scripts into a folder and then click on my
> batch file that will run all scripts in that folder, and create a log file
> with the results.
The MS-DOS FOR command is pretty powerful
RunScripts.bat
@.ECHO OFF
IF "%1"=="" GOTO Syntax
FOR %f IN (%1\*.sql) DO osql -i %f <-- also add other switches for
osql.exe
GOTO End
:Syntax
ECHO Please specify a folder like this:
ECHO RunScripts c:\scripts
ECHO to run all the SQL scripts in that folder
:End
ECHO.
Peace & happy computing,
Mike Labosh, MCSD
"SELECT * FROM Users WHERE Clue > 0"
|||> FOR %f IN (%1\*.sql) DO osql -i %f <- add other switches for
> osql.exe
> GOTO End
Sorry about the wrap, just wanted to clear this up, that line that just says
osql.exe is part of my comment on the line above it. It is not a command in
the batch file. The line above it is supposed to end with this:
<-- also add other switches for osql.exe
Peace & happy computing,
Mike Labosh, MCSD
"SELECT * FROM Users WHERE Clue > 0"
|||Hi
If you require the output from each file to be kept separate then your
method is an easy way to differentiate the output (check for empty files an
easy way to test it worked!).
If you don't require separate files my preference is to do something like:
for %f in (*.sql) do echo :r %f >> files.msql
Then run osql -E -i files.msql this will run multiple files on one
connection which should be alot faster.
John
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eiJT4TxUEHA.2028@.TK2MSFTNGP11.phx.gbl...
> Sorry about the wrap, just wanted to clear this up, that line that just
says
> osql.exe is part of my comment on the line above it. It is not a command
in
> the batch file. The line above it is supposed to end with this:
> <-- also add other switches for osql.exe
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "SELECT * FROM Users WHERE Clue > 0"
>
|||Thanks. The script is not running though... I'm getting:
c:\TEMP\runscripts c:\scripts
1\*.sql was not expected at this time
I'm not the best with %"" stuff! Any ideas?
Thanks much!
Tom
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eiJT4TxUEHA.2028@.TK2MSFTNGP11.phx.gbl...
> Sorry about the wrap, just wanted to clear this up, that line that just
says
> osql.exe is part of my comment on the line above it. It is not a command
in
> the batch file. The line above it is supposed to end with this:
> <-- also add other switches for osql.exe
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "SELECT * FROM Users WHERE Clue > 0"
>
|||> c:\TEMP\runscripts c:\scripts
> 1\*.sql was not expected at this time
OOPS! That's what I get for typing it in here and not playing with it
first. Sorry!
Here is the working version of runscripts.bat:
@.ECHO OFF
IF "%1"=="" GOTO Syntax
FOR %%f IN (%1\*.sql) DO osql -S server -E -d database -i %%f
GOTO End
:Syntax
ECHO Please specify a folder like this:
ECHO RunScripts c:\scripts
ECHO to run all the SQL scripts in that folder
:End
ECHO.
Note that %1 always has one % sign, and %%f always has two. if you look at
the first page of the help for the FOR command (C:\> HELP FOR) you will note
a statement that says if you use the FOR command in a batch file, that
%variable should be %%variable. %1, however is a parameter, not a variable.
Also, you might look at John Bell's remarks. Concatenating the scripts into
one file will reduce all the connection opening times, if that's an issue
for you. In fact, you can get the batch file to do that for you:
Contents of runscripts2.bat (tested and verified this time):
@.ECHO OFF
IF "%1"=="" GOTO Syntax
IF EXIST %TEMP%\BigScript.sql DEL %TEMP%\BigScript.sql
FOR %%f IN (%1\*.sql) DO TYPE %%f >> %TEMP%\BigScript.sql
osql -S pc5055\MLabosh -E -d Northwind -i %TEMP%\BigScript.sql
DEL %TEMP%\BigScript.sql
GOTO End
:Syntax
ECHO Please specify a folder like this:
ECHO RunScripts c:\scripts
ECHO to run all the SQL scripts in that folder
:End
ECHO.
As an option, you might insert a PAUSE right before the call to osql in case
you'd like to review bigscript.sql before running it. You could also remove
the second DEL command if you want to keep the bigscript.sql
Peace & happy computing,
Mike Labosh, MCSD
"SELECT * FROM Users WHERE Clue > 0"
|||Hi
Just to add!!
I use ~ (tilda) to remove quotes around a variable (if they exist) and then
add them manually so that spaces in file/directory names can be used.
e.g ("%~1\*.sql")
John
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:OnZwKg0UEHA.3016@.tk2msftngp13.phx.gbl...
> OOPS! That's what I get for typing it in here and not playing with it
> first. Sorry!
> Here is the working version of runscripts.bat:
> @.ECHO OFF
> IF "%1"=="" GOTO Syntax
> FOR %%f IN (%1\*.sql) DO osql -S server -E -d database -i %%f
> GOTO End
> :Syntax
> ECHO Please specify a folder like this:
> ECHO RunScripts c:\scripts
> ECHO to run all the SQL scripts in that folder
> :End
> ECHO.
> Note that %1 always has one % sign, and %%f always has two. if you look
at
> the first page of the help for the FOR command (C:\> HELP FOR) you will
note
> a statement that says if you use the FOR command in a batch file, that
> %variable should be %%variable. %1, however is a parameter, not a
variable.
> Also, you might look at John Bell's remarks. Concatenating the scripts
into
> one file will reduce all the connection opening times, if that's an issue
> for you. In fact, you can get the batch file to do that for you:
> Contents of runscripts2.bat (tested and verified this time):
> @.ECHO OFF
> IF "%1"=="" GOTO Syntax
> IF EXIST %TEMP%\BigScript.sql DEL %TEMP%\BigScript.sql
> FOR %%f IN (%1\*.sql) DO TYPE %%f >> %TEMP%\BigScript.sql
> osql -S pc5055\MLabosh -E -d Northwind -i %TEMP%\BigScript.sql
> DEL %TEMP%\BigScript.sql
> GOTO End
> :Syntax
> ECHO Please specify a folder like this:
> ECHO RunScripts c:\scripts
> ECHO to run all the SQL scripts in that folder
> :End
> ECHO.
> As an option, you might insert a PAUSE right before the call to osql in
case
> you'd like to review bigscript.sql before running it. You could also
remove
> the second DEL command if you want to keep the bigscript.sql
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "SELECT * FROM Users WHERE Clue > 0"
>
|||Thank you guys! Getting closer in that the error is gone and it is running
(some of) the scripts in the folder. In the folder c:\scripts I created two
test scripts:
Script1.sql (Use Northwind select * from orders where orderID = 10317 )
Script2.sql (Use Northwind select * from orders where employeeID = 5 )
They are both just lookups for Northwind. Both of them run fine
individually and return results, but when using the batch only one was being
returned (scripts2.sql). So, I tried swapping the names of the files to see
if only the 2nd one would run.. and this time the log showed that they both
ran.
Here's what I used:
@.ECHO OFF
IF "%1"=="" GOTO Syntax
FOR %%f IN (%1\*.sql) DO osql -Usa -Ppasswordhere -Spryancompaq -i
%%f -oC:\log.txt
GOTO End
:Syntax
ECHO Please specify a folder like this:
ECHO RunScripts c:\scripts
ECHO to run all the SQL scripts in that folder
:End
ECHO.
Could it be that it is actually running both, but that it overwrites the Log
file after the first one? If so, I wonder why it displayed both results the
2nd time. Perhaps the order of the scripts has something to do with it?
(Of course, the order they are run is important in many cases... we usually
name our scripts starting with 01script, 02script, etc.. I wonder if this
batch would run them but that order.. will try some tests..)
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uzoT5D5UEHA.412@.TK2MSFTNGP10.phx.gbl...
> Hi
> Just to add!!
> I use ~ (tilda) to remove quotes around a variable (if they exist) and
then[vbcol=seagreen]
> add them manually so that spaces in file/directory names can be used.
> e.g ("%~1\*.sql")
> John
> "Mike Labosh" <mlabosh@.hotmail.com> wrote in message
> news:OnZwKg0UEHA.3016@.tk2msftngp13.phx.gbl...
> at
> note
> variable.
> into
issue
> case
> remove
>
|||Hi
With the fixed log file you will overwrite it for each file, therefore only
the last output will be given
If you require one output file see my previous post, if infividual files are
required try:
FOR %%f IN ("%~1\*.sql") DO osql -Usa -Ppasswordhere -Spryancompaq -i %%f -o
%%~nf.txt
John
"Tom" <none@.none.com> wrote in message
news:uTBBqP6UEHA.3476@.tk2msftngp13.phx.gbl...
> Thank you guys! Getting closer in that the error is gone and it is
running
> (some of) the scripts in the folder. In the folder c:\scripts I created
two
> test scripts:
> Script1.sql (Use Northwind select * from orders where orderID = 10317 )
> Script2.sql (Use Northwind select * from orders where employeeID = 5 )
> They are both just lookups for Northwind. Both of them run fine
> individually and return results, but when using the batch only one was
being
> returned (scripts2.sql). So, I tried swapping the names of the files to
see
> if only the 2nd one would run.. and this time the log showed that they
both
> ran.
> Here's what I used:
> --
> @.ECHO OFF
> IF "%1"=="" GOTO Syntax
> FOR %%f IN (%1\*.sql) DO osql -Usa -Ppasswordhere -Spryancompaq -i
> %%f -oC:\log.txt
> GOTO End
> :Syntax
> ECHO Please specify a folder like this:
> ECHO RunScripts c:\scripts
> ECHO to run all the SQL scripts in that folder
> :End
> ECHO.
> --
> Could it be that it is actually running both, but that it overwrites the
Log
> file after the first one? If so, I wonder why it displayed both results
the
> 2nd time. Perhaps the order of the scripts has something to do with it?
> (Of course, the order they are run is important in many cases... we
usually[vbcol=seagreen]
> name our scripts starting with 01script, 02script, etc.. I wonder if this
> batch would run them but that order.. will try some tests..)
>
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:uzoT5D5UEHA.412@.TK2MSFTNGP10.phx.gbl...
> then
look[vbcol=seagreen]
will[vbcol=seagreen]
scripts[vbcol=seagreen]
> issue
in
>