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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Tom (none_at_none.com)
Date: 06/16/04 

Date: Wed, 16 Jun 2004 09:14:30 -0400

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

  • Re: how to execute batch file automatically when file is added in folder
    ... Pegasus (MVP) wrote: ... Line2 if not exist c:\OldDir.txt echo. ... Line7 rem Your batch file lines go here ... I cannot tell you what "d:My Folder" should be. ...
    (microsoft.public.windowsxp.general)
  • Re: How to Re-name a Massive Number of Files
    ... You can do this with a humble batch file, ... set Source=d:\My Folder ... echo ren "%%a" ER!Number!.pdf ... Now did you say you were prepared to pay real money for ...
    (microsoft.public.windowsxp.general)
  • XP batch File, command line Zip, how?
    ... will, zip a folder using XP?s own ... Below is an example of my simple Batch file: ... @echo off ... copy TestSource\*.* TestDest ...
    (alt.os.windows-xp)
  • Re: Creating a .bat file?
    ... echo Merry Christmas ... If you want help with the batch file you need to reveal more of its contents or you should reveal the source of the file. ... 'Delay' is not a valid Windows command, this is probably just pinging the loopback adapter for 30 seconds. ... wouldn't know their user names or profile folder path so they would ...
    (microsoft.public.windowsxp.help_and_support)
  • Re: how to execute batch file automatically when file is added in folder
    ... Line2 if not exist c:\OldDir.txt echo. ... Line7 rem Your batch file lines go here ... I cannot tell you what "d:My Folder" should be. ... i tried to execute the script as it is but it gave error device not ...
    (microsoft.public.windowsxp.general)