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

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Beeeeeves (beeeeeeeeev_at_ves)
Date: 06/20/04 

Date: Sun, 20 Jun 2004 11:57:53 +0100

The osql can take a /d switch for the database name - but anyway thanks for
that!

"John Bell" <jbellnewsposts@hotmail.com> wrote in message
news:%23YFJgnLVEHA.2520@TK2MSFTNGP10.phx.gbl...
> Hi
>
> 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" <none@none.com> wrote in message
> news:OOr%23tDKVEHA.4028@TK2MSFTNGP09.phx.gbl...
> > You're right, I could pipe them all into one file, but we often have
'USE'
> > 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" <jbellnewsposts@hotmail.com> wrote in message
> > news:%230Peh%23HVEHA.1952@TK2MSFTNGP12.phx.gbl...
> > > Hi Tom
> > >
> > > 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" <none@none.com> wrote in message
> > > news:e5Wb4IHVEHA.2872@TK2MSFTNGP10.phx.gbl...
> > > > Thank you guys so much for you help and time with this. I now have
> the
> > > log
> > > > 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" <jbellnewsposts@hotmail.com> wrote in message
> > > > news:Or1Wd$7UEHA.1888@TK2MSFTNGP11.phx.gbl...
> > > > > 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
> > > > > > 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
> > > > > > > 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...
> > > > > > > > > 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"
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages