oSQL - How do I run all .SQL files in a folder by running one batch
Tom
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
Gregory A. Larsen
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" <no...@none.com> wrote in message
news:ublnUExU...@TK2MSFTNGP10.phx.gbl...
Mike Labosh
> 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"
Mike Labosh
> 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
--
John Bell
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" <mla...@hotmail.com> wrote in message
news:eiJT4TxU...@TK2MSFTNGP11.phx.gbl...
Tom
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" <mla...@hotmail.com> wrote in message
news:eiJT4TxU...@TK2MSFTNGP11.phx.gbl...
Mike Labosh
> 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
John Bell
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" <mla...@hotmail.com> wrote in message
news:OnZwKg0U...@tk2msftngp13.phx.gbl...
Tom
(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" <jbellne...@hotmail.com> wrote in message
news:uzoT5D5...@TK2MSFTNGP10.phx.gbl...
John Bell
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" <no...@none.com> wrote in message
news:uTBBqP6U...@tk2msftngp13.phx.gbl...
Tom
files for each .sql being returned to a folder that is then piped into one
large log file. These batches are so helpful!
You guys rock!
Tom
"John Bell" <jbellne...@hotmail.com> wrote in message
news:Or1Wd$7UEHA...@TK2MSFTNGP11.phx.gbl...
John Bell
If you wanted one output file then look at my original posts that creates a
single file to execute. As this uses one connection it will be alot quicker.
John
"Tom" <no...@none.com> wrote in message
news:e5Wb4IHV...@TK2MSFTNGP10.phx.gbl...
Tom
statements b/c of two DBs and sometimes USE isn't specified, so it may run
on the wrong DB. Also, when piping into one file I've noticed before that
sometimes the end of one .sql will join with the beginning of the next and
the result is LASTLINEFIRSTLINE - joined statements that are incorrect. I
could ask that Dev here leave some spacers... but I can't count on that
happening.
"John Bell" <jbellne...@hotmail.com> wrote in message
news:%230Peh%23HVEH...@TK2MSFTNGP12.phx.gbl...
John Bell
I would suggest using three part naming and enforcing that as the standard,
this would remove the need for USE.
I am not sure what you mean by joined statements. The method I suggested
does not concatenate the SQL but runs a script that will execute each file
one after each other using a single connection.
John
"Tom" <no...@none.com> wrote in message
news:OOr%23tDKVE...@TK2MSFTNGP09.phx.gbl...
Tom
running that script.
I'll give it a shot then.
Gracais!
"John Bell" <jbellne...@hotmail.com> wrote in message
news:%23YFJgnL...@TK2MSFTNGP10.phx.gbl...
Beeeeeves
Can't wait to get back to work to try it....bloody hell! if it does work,
you've made my year.
"John Bell" <jbellne...@hotmail.com> wrote in message
news:%23YFJgnL...@TK2MSFTNGP10.phx.gbl...
Beeeeeves
that!
"John Bell" <jbellne...@hotmail.com> wrote in message
news:%23YFJgnL...@TK2MSFTNGP10.phx.gbl...
John Bell
According to the documentation this issues a USE statement after connection
so if you use three part naming then /d is not really needed!! When running
multiple scripts the missing USE statements will still be a problem.
e.g even thought I specify pubs the USE in script1.sql will remain
C:\testsql>type scriptall.sql
:r script1.sql
:r script2.sql
C:\testsql>type script1.sql
Use Northwind
select * from orders where orderID = 10317
C:\testsql>type script2.sql
select * from authors
C:\testsql>osql -E -d Pubs -i scriptall.sql
1> 2> 3> OrderID CustomerID EmployeeID OrderDate
RequiredDate ShippedDate ShipVia
Freight ShipName
ShipAddress
ShipCity ShipRegion ShipPostalCode ShipCountry
----------- ---------- ----------- -----------------------
----------------------- ----------------------- -----------
--------------------- ----------------------------------------
------------------------------------------------------------
--------------- --------------- -------------- ---------------
10317 LONEP 6 1996-09-30 00:00:00.000
1996-10-28 00:00:00.000 1996-10-10 00:00:00.000 1
12.6900 Lonesome Pine Restaurant
89 Chiaroscuro Rd.
Portland OR 97219 USA
(1 row affected)
Msg 208, Level 16, State 1, Server CARILLONNB, Line 1
Invalid object name 'authors'.
C:\testsql>
As a fudge you may want to change the way your scriptall.sql is constructed
to enforce a default.
C:\testsql>type scriptall.sql
USE PUBS
:r script1.sql
USE PUBS
:r script2.sql
John
"Beeeeeves" <beeeeeeeeev@ves> wrote in message
news:%23iwNOWr...@TK2MSFTNGP11.phx.gbl...
Vlad Vissoultchev
script1.sql:
<batch1>
GO
<batch2>
GO
cheers,
</wqw>
John Bell
I can not find anything that talks about having go statements in the script
files! With the GOs there you need to use the other method.
Again implementing a standard for these files would make this activity alot
less painful.
John
"Vlad Vissoultchev" <wqw...@nospam.myrealbox.com> wrote in message
news:%23vuXeCv...@tk2msftngp13.phx.gbl...