Osql Syntax Help

Dear friends,

I was trying to run this script from a dos batch file under win xp but it's not working. Please help with the syntax.
================================================== =======
OSQL -U sa -P samsde -S firebird /Q "RESTORE DATABASE NAVIMEX FROM DISK = 'C:\30704' WITH

REPLACE, MOVE'NAVIMEX_Data' TO 'C:\Program Files\Microsoft SQL

Server\MSSQL\Data\NAVIMEX_Data.MDF',MOVE 'NAVIMEX_Log' TO 'C:\Program Files\Microsoft SQL

Server\MSSQL\Data\NAVIMEX_LOG.LDF'" QUIT
================================================== ========

Please provide me with the correct syntax to be put in a 'restore.bat' file :rolleyes:

Thanks in advance.
HotBirdWhat errors are you getting? You can add "/o<filename>" or use command line redirection to capture all errors. And don't use QUIT unless your RESTORE was unsuccessful.|||make sure that you dont hit enter after "microsoft sql" in the path
try a shorter path
also i believe that when you issue a restore cmd from disk, you have to specify the file name
ex
from disk = 'c:\data\northwind.mdf'

to back up robert, what errors do you get?|||Dear Friends,

Thank you for your replies,

The script works but I'm facing a problem while trying to restore from a network drive or a remote path (\\xxx.xxx.xxx.xxx\mypath\filename)

can you help please?

Regards,

Samer Chaer :rolleyes:|||It's usually due to a lack of necessary permissions on behalf of the security context under which the process is invoked.

osql question

Re:
osql -i test.sql -e -b -r 1 > temp.log
I intend to use osql to submit a bunch of SQL scripts from
a batch file.
If the SQL script has an error, the message is logged with
a line number. But the echoed input does not have line
numbers. Instead, the SQL text is preceded by a line that
looks like:
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17>
18> 19> 20> 21> 22> 23>
The above looks to me like the line numbers without the
text. The text is successfully echoed after that, but
with out line numbers.
Is there any way to get the text echoed to the log file
with line numbers?
Hi
I don't think you can do exactly what you want. -n will turn of the N>
prompts and -m can set the error level to display.
John
"Randy" <anonymous@.discussions.microsoft.com> wrote in message
news:1c7c801c4524c$36dbc0a0$a001280a@.phx.gbl...
> Re:
> osql -i test.sql -e -b -r 1 > temp.log
> I intend to use osql to submit a bunch of SQL scripts from
> a batch file.
> If the SQL script has an error, the message is logged with
> a line number. But the echoed input does not have line
> numbers. Instead, the SQL text is preceded by a line that
> looks like:
> 1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17>
> 18> 19> 20> 21> 22> 23>
> The above looks to me like the line numbers without the
> text. The text is successfully echoed after that, but
> with out line numbers.
> Is there any way to get the text echoed to the log file
> with line numbers?
>
>

OSQL question

Hi I am trying to write a batch file that will give me all records relating to the query.

osql -S sqlserver -d database -U sa -P "" -Q "select docloc from mhgroup.docmaster where docloc like '%1054193%'"

The query is truncating the 1054193 to 054193....

Is there a problem with using LIKE and the % wildcard in an OSQL statement?

Thanks,
Darrin Carterhow do you know it's truncating? did you see it in profiler? i just ran it here (slightly modified) and it runs correctly, profiler shows the exact string.|||Originally posted by ms_sql_dba
how do you know it's truncating? did you see it in profiler? i just ran it here (slightly modified) and it runs correctly, profiler shows the exact string.

I run it from the batch file that I created. In the DOS window it is missing the 1 at the beginning as well as the % signs

This is the actual statement:

osql -S sqlserver -d database -U sa -P "" -Q "select docloc from mhgroup.docmaster where docloc like '%1054193%'"

This is how it shows up in the DOS window.

I:\>osql -S sqlserver -d database -U sa -P "" -I -Q "select docloc from mhgroup.docmaster where docloc like 054193"|||ok, now I get it. %1 is a replacement for the first argument following the batch file name, which is the reason why %1 disappears. I am not sure about the other % sign.|||you probably will have to use DOS escape codes to make %' visible to OSQL.|||Just use two percent signs (eg : %%) to make one visible to DOS

eg your actual statement should be :

osql -S sqlserver -d database -U sa -P "" -Q "select docloc from mhgroup.docmaster where docloc like '%%1054193%%'"|||Originally posted by Enigma
Just use two percent signs (eg : %%) to make one visible to DOS

eg your actual statement should be :

osql -S sqlserver -d database -U sa -P "" -Q "select docloc from mhgroup.docmaster where docloc like '%%1054193%%'"

That did it. Thanks!

OSQL prompts for password

Hello,
I have a batch file setup for backing up an msde 2000 database but the only
way it will run is by inserting -Usa -Ppassword. I don't want to expose the
sa password and I would like to use windows authentication so I've included
the -E switch. However, it prompts for a password and no matter what I put
in it fails with invalid password and my user login name. The password that
I enter is correct and the server is setup with Windows Authentication. The
batch file is called from an application and I don't want the user to to
have to enter a password, I just want the process to run when started.
Here's How I'm calling the sql script.
OSQL -E -iTestBackup.sql
TIA
Vic
Problem Solved! The switch should be uppercase E and I had lower case which
means echo.

OSQL prompts for password

Hello,
I have a batch file setup for backing up an msde 2000 database but the only
way it will run is by inserting -Usa -Ppassword. I don't want to expose the
sa password and I would like to use windows authentication so I've included
the -E switch. However, it prompts for a password and no matter what I put
in it fails with invalid password and my user login name. The password that
I enter is correct and the server is setup with Windows Authentication. The
batch file is called from an application and I don't want the user to to
have to enter a password, I just want the process to run when started.
Here's How I'm calling the sql script.
OSQL -E -iTestBackup.sql
TIA
VicProblem Solved! The switch should be uppercase E and I had lower case which
means echo.

OSQL prompts for password

Hello,
I have a batch file setup for backing up an msde 2000 database but the only
way it will run is by inserting -Usa -Ppassword. I don't want to expose the
sa password and I would like to use windows authentication so I've included
the -E switch. However, it prompts for a password and no matter what I put
in it fails with invalid password and my user login name. The password that
I enter is correct and the server is setup with Windows Authentication. The
batch file is called from an application and I don't want the user to to
have to enter a password, I just want the process to run when started.
Here's How I'm calling the sql script.
OSQL -E -iTestBackup.sql
TIA
VicProblem Solved! The switch should be uppercase E and I had lower case which
means echo.

osql or isql to run a batch remotely

Hello,
I have a collection of SQL Scripts that I would like to execute using a
batch file from a remote machine. Does anyone have any suggestions? I
think that I could use osql or isql, but I have never done that before. I
would appreciate any assistance.
Stewart
Stewart Saathoff wrote:
> Hello,
> I have a collection of SQL Scripts that I would like to execute using
> a batch file from a remote machine. Does anyone have any
> suggestions? I think that I could use osql or isql, but I have never
> done that before. I would appreciate any assistance.
> Stewart
I'm unclear about what is where...
Where is the file you want to execute located?
Do you want to run this file from a remote client or do you want to run
the file as though it were running directly on the server?
You can kick off OSQL from a stored procedure or a SQL Agent job on the
server. You could kick off OSQL from a client PC and execute a script
file that resides on the client PC. I would say that if the script file
is large, you'll get better performance running it from the server.
David Gugick
Imceda Software
www.imceda.com
|||ok, Let me be more clear.
I have a very large SQL Statement that needs to be run on the server.
Ideally the client will sit on their workstation and double-click on a file.
That file should execute the SQL statement on the server. I am pretty
positive that I can do all of this using osql, however I am unclear how I
can do this using a batch file. I don't want the users to have to type a
considerable amount and I definately do not want them to have to learn osql
or sql commands to do this...
Stewart
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%237j3Oby1EHA.524@.TK2MSFTNGP09.phx.gbl...
> Stewart Saathoff wrote:
> I'm unclear about what is where...
> Where is the file you want to execute located?
> Do you want to run this file from a remote client or do you want to run
> the file as though it were running directly on the server?
> You can kick off OSQL from a stored procedure or a SQL Agent job on the
> server. You could kick off OSQL from a client PC and execute a script file
> that resides on the client PC. I would say that if the script file is
> large, you'll get better performance running it from the server.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||Stewart Saathoff wrote:
> ok, Let me be more clear.
> I have a very large SQL Statement that needs to be run on the server.
> Ideally the client will sit on their workstation and double-click on
> a file. That file should execute the SQL statement on the server. I
> am pretty positive that I can do all of this using osql, however I am
> unclear how I can do this using a batch file. I don't want the users
> to have to type a considerable amount and I definately do not want
> them to have to learn osql or sql commands to do this...
> Stewart
>
If you have to run the SQL batch from a client PC, you can use OSQL.
There's an option for submitting a file to run. The command-line
interface is fairly straightforward and documented in BOL.
David Gugick
Imceda Software
www.imceda.com
|||<snip>

> I am pretty positive that I can do all of this using
> osql, however I am unclear how I can do this using a
> batch file.
<snip>
Stewart,
Assuming you get this to work at the command prompt...
osql -SBigBox -Uuser -Ppwd -ddbname -i somefile.sql -o somefile.log
You can just paste it into a BAT file: that's it. You can use @.ECHO OFF,
print out message, etc to pretty it up but that's pretty much all you need.
You would need a file with your SQL statement where the BAT file runs
(ideally the same folder). If that's not acceptable, then you could put the
statement in a stored proc and call the proc by name instead of using the
input file.
Craig
|||Thank you all for your help. Everything works great.
"Craig Kelly" <cnkelly.nospam@.nospam.net> wrote in message
news:yY9rd.1007069$Gx4.741632@.bgtnsc04-news.ops.worldnet.att.net...
> <snip>
>
> <snip>
> Stewart,
> Assuming you get this to work at the command prompt...
> osql -SBigBox -Uuser -Ppwd -ddbname -i somefile.sql -o somefile.log
> You can just paste it into a BAT file: that's it. You can use @.ECHO OFF,
> print out message, etc to pretty it up but that's pretty much all you
> need.
> You would need a file with your SQL statement where the BAT file runs
> (ideally the same folder). If that's not acceptable, then you could put
> the statement in a stored proc and call the proc by name instead of using
> the input file.
> Craig
>

osql batch processing without -i

Hi,
I have just started using MSSQL and the DOS environment at work. I have
a lot of experience with Sybase and the UNIX environment, but this is a
whole new ball of wax.
I'd like to use osql from a batch file to log into the dataserver and
run a fairly long list of SQL and then exit. I don't want to have a bunch
of SQL files sitting around that I have to use the -i option to run, and
I'd rather not create temporary SQL files like this.

echo "exec sp_who2" >tmp.sql
echo "select * from...... " >>tmp.sql
osql -E -S <DATASERVER> -n -w999 -i tmp.sql
del tmp.sql

I can't use the -Q option, of course because as I said, I'll be writing
quite a few lines of SQL, and it won't all fit on the one line, or at least
it wouldn't be pretty if I did.

In UNIX, I can simply execute the following from either the command line
or in
a script.

isql -S<DATASERVER> -U<USER> <<-EOF

sp_who
go
select * from ....
go
EOF --EOF is the isql session terminator exits me back to the command line.

This behavior does not appear to work with OSQL. There is a -O option
which help mentions, and that talks about disabling the EOF terminator for
batch processing, but I wasn't able to find any usage or examples on the net
where someone is using EOF to terminate their OSQL SQL batch.

This is what help listed.
[-O use Old ISQL behavior disables the following]
<EOF> batch processing
Auto console width scaling
Wide messages
default errorlevel is -1 vs 1

Any help you could offer would be appreciated. Thanks.

DarrenOn Mon, 16 Jan 2006 18:44:19 -0600, <dphillip79@.comcast.net> wrote:

(snip)
> In UNIX, I can simply execute the following from either the command line
>or in
>a script.
>isql -S<DATASERVER> -U<USER> <<-EOF
>sp_who
>go
>select * from ....
>go
>EOF --EOF is the isql session terminator exits me back to the command line.
> This behavior does not appear to work with OSQL.

Hi,

It's not OSQL, it's Windows/DOS that is causing the difference. In UNIX,
utilities take their input from STDIN, which is either the next line in
the script (if run from a script) or the next line entered on the
console (if run from a console).

The same thing happens if you create a script (testme.bat) with the
following content:

copy con test.txt
This won't work

Execute it. The copy con command is started and the console will wait
for your input. Type one or more lines, then type Ctrl-Z (the EOF
marker). You'll next see an error because "Thins won't work" is not a
valid DOS command.

An equivalent script in Unix would enter "This won't work" in the file
test.txt.

> Any help you could offer would be appreciated.

I think that you'll have to settle for either a bunch of commonly used
SQL script files, or for dynamically building the SQL script using a
bunch of redirected echo statements.

--
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info> wrote in message
news:raiqs1pfephh7faud9sesnjot56c7tdng9@.4ax.com...
> On Mon, 16 Jan 2006 18:44:19 -0600, <dphillip79@.comcast.net> wrote:
> (snip)
> > In UNIX, I can simply execute the following from either the command
line
> >or in
> >a script.
> >isql -S<DATASERVER> -U<USER> <<-EOF
> >sp_who
> >go
> >select * from ....
> >go
> >EOF --EOF is the isql session terminator exits me back to the command
line.
> > This behavior does not appear to work with OSQL.
> Hi,
> It's not OSQL, it's Windows/DOS that is causing the difference. In UNIX,
> utilities take their input from STDIN, which is either the next line in
> the script (if run from a script) or the next line entered on the
> console (if run from a console).
> The same thing happens if you create a script (testme.bat) with the
> following content:
> copy con test.txt
> This won't work
> Execute it. The copy con command is started and the console will wait
> for your input. Type one or more lines, then type Ctrl-Z (the EOF
> marker). You'll next see an error because "Thins won't work" is not a
> valid DOS command.
> An equivalent script in Unix would enter "This won't work" in the file
> test.txt.
> > Any help you could offer would be appreciated.
> I think that you'll have to settle for either a bunch of commonly used
> SQL script files, or for dynamically building the SQL script using a
> bunch of redirected echo statements.

Thanks for the Info Hugo. I was afraid of that. I'm used being able to
do whatever I want in UNIX, so DOS seems to be a bit of a step back for me.
I did just download a version of sed for DOS, which I'm really excited
about. :) I also have a VB .net class next week, so I'm hoping to pick up
some tricks in there. Thanks again.

Darren

> --
> Hugo Kornelis, SQL Server MVP

OSQL Batch File Problem

I have a scheduled task that runs a batch file in windows 2003 server which
is running SQL Server 2000 Enterprise.
The batch file contains the line:
OSQL -i myscript.sql -Umyuser -Pmypassword -Slocalhost -o mytrans.log
the 'myscript.sql' file updates a table with another table of the exact
number of rows.
When the scheduled task runs, the 'mytrans.log' file shows 0 rows were
affected.
If I run this myself from the command line, 22,000 rows are affected which
is correct.
I am guessing there is some sort of permission/authentication issue here. I
am sending the right username/password for SQL and for windows to run the
task.
Any idea what I could do to fix this?
Thanks,
J. BaezGo to schedule tasks -- Tasks tab--See the user in RUN AS: . The user
displayed there should be having write access to the folder you are writing
the log file.
After that just right click and run the task and see.
Thanks
hari
SQL Server MVP
"J. Baez" wrote:
> I have a scheduled task that runs a batch file in windows 2003 server which
> is running SQL Server 2000 Enterprise.
> The batch file contains the line:
> OSQL -i myscript.sql -Umyuser -Pmypassword -Slocalhost -o mytrans.log
> the 'myscript.sql' file updates a table with another table of the exact
> number of rows.
> When the scheduled task runs, the 'mytrans.log' file shows 0 rows were
> affected.
> If I run this myself from the command line, 22,000 rows are affected which
> is correct.
> I am guessing there is some sort of permission/authentication issue here. I
> am sending the right username/password for SQL and for windows to run the
> task.
> Any idea what I could do to fix this?
> Thanks,
> J. Baez|||That user already has full control of that directory. So it still not
working. Any other ideas?
"Hari Prasad" wrote:
> Go to schedule tasks -- Tasks tab--See the user in RUN AS: . The user
> displayed there should be having write access to the folder you are writing
> the log file.
> After that just right click and run the task and see.
> Thanks
> hari
> SQL Server MVP
> "J. Baez" wrote:
> > I have a scheduled task that runs a batch file in windows 2003 server which
> > is running SQL Server 2000 Enterprise.
> >
> > The batch file contains the line:
> >
> > OSQL -i myscript.sql -Umyuser -Pmypassword -Slocalhost -o mytrans.log
> >
> > the 'myscript.sql' file updates a table with another table of the exact
> > number of rows.
> >
> > When the scheduled task runs, the 'mytrans.log' file shows 0 rows were
> > affected.
> >
> > If I run this myself from the command line, 22,000 rows are affected which
> > is correct.
> >
> > I am guessing there is some sort of permission/authentication issue here. I
> > am sending the right username/password for SQL and for windows to run the
> > task.
> >
> > Any idea what I could do to fix this?
> >
> > Thanks,
> >
> > J. Baez

OSQL Batch File Problem

I have a scheduled task that runs a batch file in windows 2003 server which
is running SQL Server 2000 Enterprise.
The batch file contains the line:
OSQL -i myscript.sql -Umyuser -Pmypassword -Slocalhost -o mytrans.log
the 'myscript.sql' file updates a table with another table of the exact
number of rows.
When the scheduled task runs, the 'mytrans.log' file shows 0 rows were
affected.
If I run this myself from the command line, 22,000 rows are affected which
is correct.
I am guessing there is some sort of permission/authentication issue here. I
am sending the right username/password for SQL and for windows to run the
task.
Any idea what I could do to fix this?
Thanks,
J. BaezTry fully using fully qualified path for the input file -
C:\SomeFolder\mysqlscript.sql
or whatever the path is.
-Sue
.
On Tue, 22 Aug 2006 05:26:01 -0700, J. Baez
<JBaez@.discussions.microsoft.com> wrote:

>I have a scheduled task that runs a batch file in windows 2003 server which
>is running SQL Server 2000 Enterprise.
>The batch file contains the line:
>OSQL -i myscript.sql -Umyuser -Pmypassword -Slocalhost -o mytrans.log
>the 'myscript.sql' file updates a table with another table of the exact
>number of rows.
>When the scheduled task runs, the 'mytrans.log' file shows 0 rows were
>affected.
>If I run this myself from the command line, 22,000 rows are affected which
>is correct.
>I am guessing there is some sort of permission/authentication issue here.
I
>am sending the right username/password for SQL and for windows to run the
>task.
>Any idea what I could do to fix this?
>Thanks,
>J. Baez|||It is really, really helpful if you post ONLY one copy of your request.
Posting in multiple locations WITHOUT crossposting loses you the benefit of
our 'bouncing' ideas off of each other.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"J. Baez" <JBaez@.discussions.microsoft.com> wrote in message
news:90E8F840-025C-4FFE-8926-55FC25F94567@.microsoft.com...
>I have a scheduled task that runs a batch file in windows 2003 server which
> is running SQL Server 2000 Enterprise.
> The batch file contains the line:
> OSQL -i myscript.sql -Umyuser -Pmypassword -Slocalhost -o mytrans.log
> the 'myscript.sql' file updates a table with another table of the exact
> number of rows.
> When the scheduled task runs, the 'mytrans.log' file shows 0 rows were
> affected.
> If I run this myself from the command line, 22,000 rows are affected which
> is correct.
> I am guessing there is some sort of permission/authentication issue here.
> I
> am sending the right username/password for SQL and for windows to run the
> task.
> Any idea what I could do to fix this?
> Thanks,
> J. Baez
>|||Yeah...I noticed that after I replied. I think there are all
different answers, directions trying to help out with this
one on at least a few different groups. And no one knows who
is suggesting what.
It also makes it hard for others to find the answer when
they search things in the future. One post has the answer,
others don't, you only find the one without the solution,
etc.
-Sue
On Tue, 22 Aug 2006 13:19:49 -0700, "Arnie Rowland"
<arnie@.1568.com> wrote:

>It is really, really helpful if you post ONLY one copy of your request.
>Posting in multiple locations WITHOUT crossposting loses you the benefit of
>our 'bouncing' ideas off of each other.

OSQL Batch File Problem

I have a scheduled task that runs a batch file in windows 2003 server which
is running SQL Server 2000 Enterprise.
The batch file contains the line:
OSQL -i myscript.sql -Umyuser -Pmypassword -Slocalhost -o mytrans.log
the 'myscript.sql' file updates a table with another table of the exact
number of rows.
When the scheduled task runs, the 'mytrans.log' file shows 0 rows were
affected.
If I run this myself from the command line, 22,000 rows are affected which
is correct.
I am guessing there is some sort of permission/authentication issue here. I
am sending the right username/password for SQL and for windows to run the
task.
Any idea what I could do to fix this?
Thanks,
J. BaezGo to schedule tasks -- Tasks tab--See the user in RUN AS: . The user
displayed there should be having write access to the folder you are writing
the log file.
After that just right click and run the task and see.
Thanks
hari
SQL Server MVP
"J. Baez" wrote:

> I have a scheduled task that runs a batch file in windows 2003 server whic
h
> is running SQL Server 2000 Enterprise.
> The batch file contains the line:
> OSQL -i myscript.sql -Umyuser -Pmypassword -Slocalhost -o mytrans.log
> the 'myscript.sql' file updates a table with another table of the exact
> number of rows.
> When the scheduled task runs, the 'mytrans.log' file shows 0 rows were
> affected.
> If I run this myself from the command line, 22,000 rows are affected which
> is correct.
> I am guessing there is some sort of permission/authentication issue here.
I
> am sending the right username/password for SQL and for windows to run the
> task.
> Any idea what I could do to fix this?
> Thanks,
> J. Baez|||That user already has full control of that directory. So it still not
working. Any other ideas?
"Hari Prasad" wrote:
[vbcol=seagreen]
> Go to schedule tasks -- Tasks tab--See the user in RUN AS: . The user
> displayed there should be having write access to the folder you are writin
g
> the log file.
> After that just right click and run the task and see.
> Thanks
> hari
> SQL Server MVP
> "J. Baez" wrote:
>

osql batch

Hi
I want to run a batch file containg osql commands but do not want the user
to be able to see the sa password. Is there any way of doing this?
Thanks> I want to run a batch file containg osql commands but do not want the user
> to be able to see the sa password. Is there any way of doing this?
Use trusted connection (-E parameter) instead of sql login/password.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Thanks
"Dejan Sarka" wrote:
> > I want to run a batch file containg osql commands but do not want the user
> > to be able to see the sa password. Is there any way of doing this?
> Use trusted connection (-E parameter) instead of sql login/password.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>

osql batch

Hi
I want to run a batch file containg osql commands but do not want the user
to be able to see the sa password. Is there any way of doing this?
Thanks
> I want to run a batch file containg osql commands but do not want the user
> to be able to see the sa password. Is there any way of doing this?
Use trusted connection (-E parameter) instead of sql login/password.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Thanks
"Dejan Sarka" wrote:

> Use trusted connection (-E parameter) instead of sql login/password.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>

osql batch

Hi
I want to run a batch file containg osql commands but do not want the user
to be able to see the sa password. Is there any way of doing this?
Thanks> I want to run a batch file containg osql commands but do not want the user
> to be able to see the sa password. Is there any way of doing this?
Use trusted connection (-E parameter) instead of sql login/password.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Thanks
"Dejan Sarka" wrote:

> Use trusted connection (-E parameter) instead of sql login/password.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>

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,
TomI 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
> 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[vbcol=seagreen]
> 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
> 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[vbcol=seagreen]
>

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
>

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
>

OSQL

I have a question with using osql. I'vce got a batch file that executes a
series of SQL scripts. These SQL scripts each will take between one and two
hours to execute. What I think is happening is that when one script fires
off, the next in line immediately fires.
How can I tell the bach file (osql) to wait until the script being executed
is complete before moving onto the next one?
Thanks in advance!
rem THIS BATCH FILE GENERATES THE SCRIPTS FOR REPLICATION
rem ONCE SCRIPTS ARE GENERATED THE BATCH FILE EXECUTES THEM IN THE PROPER
ORDER
echo GENERATING DROPSTATEMENTS
osql -Usa -Pvirtual -SACHIEVEREPL -dzion /Q "EXEC
dbo.GenerateReplicationDropStatements '5.0.4'" -o"E:\Documentation\CONVERSIO
N
SCRIPTS\droparticles2.sql"
echo GENERATING ADDSTATEMENTS
osql -Usa -Pvirtual -SACHIEVEREPL -dzion /Q "EXEC
dbo.GenerateReplicationAddStatements '5.0.4'" -o"E:\Documentation\CONVERSION
SCRIPTS\addarticles2.sql"
echo DROPPING ARTICLES
osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
SCRIPTS\droparticles2.sql" -o"E:\Documentation\Drop.log"
echo EXECUTING RELEASE SCRIPTS
osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
SCRIPTS\Script1-Cleanup.SQL" -o"E:\Documentation\CONVERSION
SCRIPTS\Script1-Cleanup.log"
osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
SCRIPTS\Script2-TableChanges.SQL" -o"E:\Documentation\CONVERSION
SCRIPTS\Script2-TableChanges.log"
osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
SCRIPTS\Script3-DropUnusedObjects.SQL" -o"E:\Documentation\CONVERSION
SCRIPTS\Script3-DropUnusedObjects.log"
osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
SCRIPTS\Script4-ChangedObjects.SQL" -o"E:\Documentation\CONVERSION
SCRIPTS\Script4-ChangedObjects.log"
osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
SCRIPTS\Script5-SynchEnvironments.SQL" -o"E:\Documentation\CONVERSION
SCRIPTS\Script5-SynchEnvironments.log"
osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
SCRIPTS\Script6-NewObj.SQL" -o"E:\Documentation\CONVERSION
SCRIPTS\Script6-NewObj.log"
osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
SCRIPTS\Script7- Conversion_Script_AR_ChargeRecord_PaidDa
te_Set.sql"
-o"E:\Documentation\CONVERSION
SCRIPTS\Script7- Conversion_Script_AR_ChargeRecord_PaidDa
te_Set.log"
osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
SCRIPTS\Script8- Conversion_Script_AR_ChargeRecords_Mark_
Prebills_Posted.sql"
-o"E:\Documentation\CONVERSION
SCRIPTS\Script8- Conversion_Script_AR_ChargeRecords_Mark_
Prebills_Posted.log"
osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
SCRIPTS\Script9-DBData.SQL" -o"E:\Documentation\CONVERSION
SCRIPTS\Script9-DBData.log"
osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
SCRIPTS\Script10-SecurityTokenSetup.SQL" -o"E:\Documentation\CONVERSION
SCRIPTS\Script10-SecurityTokenSetup.log"
echo COMPLETED CONVERSION SCRIPTS
echo ADDING ARTICLES
osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
SCRIPTS\addarticles2.sql" -o"E:\Documentation\ADD.log"
echo STATRING SNAPSHOT AGENT
osql -Usa -Pvirtual -SACHIEVEREPL -dmsdb /Q "EXEC sp_start_job
'ACHIEVEREPL-zion-zion-6'" -o"E:\Documentation\CONVERSION
SCRIPTS\startjob.log"
I've included the actual batch file:YOu could use the Start [Command] /wait for it.
HTH, Jens Suessmeyer.
http://www-sqlserver2005.de
--
"A. Robinson" <ARobinson@.discussions.microsoft.com> schrieb im Newsbeitrag
news:69672621-DF68-442E-A3D8-B914FDA88BFB@.microsoft.com...
>I have a question with using osql. I'vce got a batch file that executes a
> series of SQL scripts. These SQL scripts each will take between one and
> two
> hours to execute. What I think is happening is that when one script fires
> off, the next in line immediately fires.
> How can I tell the bach file (osql) to wait until the script being
> executed
> is complete before moving onto the next one?
> Thanks in advance!
> rem THIS BATCH FILE GENERATES THE SCRIPTS FOR REPLICATION
> rem ONCE SCRIPTS ARE GENERATED THE BATCH FILE EXECUTES THEM IN THE PROPER
> ORDER
> echo GENERATING DROPSTATEMENTS
> osql -Usa -Pvirtual -SACHIEVEREPL -dzion /Q "EXEC
> dbo.GenerateReplicationDropStatements
> '5.0.4'" -o"E:\Documentation\CONVERSION
> SCRIPTS\droparticles2.sql"
> echo GENERATING ADDSTATEMENTS
> osql -Usa -Pvirtual -SACHIEVEREPL -dzion /Q "EXEC
> dbo.GenerateReplicationAddStatements
> '5.0.4'" -o"E:\Documentation\CONVERSION
> SCRIPTS\addarticles2.sql"
> echo DROPPING ARTICLES
> osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
> SCRIPTS\droparticles2.sql" -o"E:\Documentation\Drop.log"
> echo EXECUTING RELEASE SCRIPTS
> osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
> SCRIPTS\Script1-Cleanup.SQL" -o"E:\Documentation\CONVERSION
> SCRIPTS\Script1-Cleanup.log"
> osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
> SCRIPTS\Script2-TableChanges.SQL" -o"E:\Documentation\CONVERSION
> SCRIPTS\Script2-TableChanges.log"
> osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
> SCRIPTS\Script3-DropUnusedObjects.SQL" -o"E:\Documentation\CONVERSION
> SCRIPTS\Script3-DropUnusedObjects.log"
> osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
> SCRIPTS\Script4-ChangedObjects.SQL" -o"E:\Documentation\CONVERSION
> SCRIPTS\Script4-ChangedObjects.log"
> osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
> SCRIPTS\Script5-SynchEnvironments.SQL" -o"E:\Documentation\CONVERSION
> SCRIPTS\Script5-SynchEnvironments.log"
> osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
> SCRIPTS\Script6-NewObj.SQL" -o"E:\Documentation\CONVERSION
> SCRIPTS\Script6-NewObj.log"
> osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
> SCRIPTS\Script7- Conversion_Script_AR_ChargeRecord_PaidDa
te_Set.sql"
> -o"E:\Documentation\CONVERSION
> SCRIPTS\Script7- Conversion_Script_AR_ChargeRecord_PaidDa
te_Set.log"
> osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
> SCRIPTS\Script8- Conversion_Script_AR_ChargeRecords_Mark_
Prebills_Posted.sq
l"
> -o"E:\Documentation\CONVERSION
> SCRIPTS\Script8- Conversion_Script_AR_ChargeRecords_Mark_
Prebills_Posted.lo
g"
> osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
> SCRIPTS\Script9-DBData.SQL" -o"E:\Documentation\CONVERSION
> SCRIPTS\Script9-DBData.log"
> osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
> SCRIPTS\Script10-SecurityTokenSetup.SQL" -o"E:\Documentation\CONVERSION
> SCRIPTS\Script10-SecurityTokenSetup.log"
> echo COMPLETED CONVERSION SCRIPTS
> echo ADDING ARTICLES
> osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
> SCRIPTS\addarticles2.sql" -o"E:\Documentation\ADD.log"
> echo STATRING SNAPSHOT AGENT
> osql -Usa -Pvirtual -SACHIEVEREPL -dmsdb /Q "EXEC sp_start_job
> 'ACHIEVEREPL-zion-zion-6'" -o"E:\Documentation\CONVERSION
> SCRIPTS\startjob.log"
>
> I've included the actual batch file:
>|||I don't see START as being an option for osql...
Could you give me an example?
Thanks!
"Jens Sü?meyer" wrote:

> YOu could use the Start [Command] /wait for it.
> HTH, Jens Suessmeyer.
> --
> http://www-sqlserver2005.de
> --
> "A. Robinson" <ARobinson@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:69672621-DF68-442E-A3D8-B914FDA88BFB@.microsoft.com...
>
>|||STart is a commandline util from DOS:
START OSQL -E -Q"Select GETDATE()" / wait
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"A. Robinson" <ARobinson@.discussions.microsoft.com> schrieb im Newsbeitrag
news:02BEFE97-165D-4D4C-B2F0-1939F6F9A786@.microsoft.com...
>I don't see START as being an option for osql...
> Could you give me an example?
> Thanks!
> "Jens Smeyer" wrote:
>|||I put START's in the script and the individual scripts did not wait for the
next ones to finish...it opens multiple osql windows and they are running
simultaneously.
I need for the scripts to run one at a time and wait for the command to
finish without opening up multiple sessions
"Jens Sü?meyer" wrote:

> STart is a commandline util from DOS:
> START OSQL -E -Q"Select GETDATE()" / wait
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "A. Robinson" <ARobinson@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:02BEFE97-165D-4D4C-B2F0-1939F6F9A786@.microsoft.com...
>
>|||Wouldn't it be easier to just combine all your scripts (seperated by go's )
and send them to OSQL once...
THen OSQL would run each statement in turn...
It seems to me that you are working too hard..
:)
"A. Robinson" wrote:
> I put START's in the script and the individual scripts did not wait for th
e
> next ones to finish...it opens multiple osql windows and they are running
> simultaneously.
> I need for the scripts to run one at a time and wait for the command to
> finish without opening up multiple sessions
> "Jens Sü?meyer" wrote:
>|||Rob:
I'd love to, but unfortunately Development isn't going to afford me such a
luxury...
"rob lynch" wrote:
> Wouldn't it be easier to just combine all your scripts (seperated by go's
)
> and send them to OSQL once...
> THen OSQL would run each statement in turn...
> It seems to me that you are working too hard..
> :)
>
> "A. Robinson" wrote:
>|||Is it the need for the Scripts that you generate in different scripts for th
e
purpose of debugging install issues?
If so, what about buffering the results in a temptable and then building the
textfiles using the output..
Another option might be to have OSQL call itself :)
I.e.
In you script and after completion...
exec master..xp_cmdshell 'osql blabalabal'
exec master..xp_cmdshell 'nextstep'
That might give you better control of the execution...
I find OSQL to be a very usefull tool and xp_cmdshell to be even more so!!!
Rob
"A. Robinson" wrote:
> Rob:
> I'd love to, but unfortunately Development isn't going to afford me such a
> luxury...
> "rob lynch" wrote:
>|||THe first two scripts that generate SQL files aren't a problem. It's the
scripts that perform DML. There are UPDATES and DELETES that take place and
each script performs a ton of operations.
The scripts end up stepping all over each other. There isn't a whole lot of
need for debugging.
Example: in the first script there is an ALTER done to a bunch of tables. In
the second script there is an ALTER done to a bunch of stored procedures tha
t
are dependent on the table changes. If the second script runs before the
first is complete, the second script will blow up because the table
definitions will not match...
"rob lynch" wrote:
> Is it the need for the Scripts that you generate in different scripts for
the
> purpose of debugging install issues?
> If so, what about buffering the results in a temptable and then building t
he
> textfiles using the output..
> Another option might be to have OSQL call itself :)
> I.e.
> In you script and after completion...
> exec master..xp_cmdshell 'osql blabalabal'
> exec master..xp_cmdshell 'nextstep'
> That might give you better control of the execution...
> I find OSQL to be a very usefull tool and xp_cmdshell to be even more so!!
!
> Rob
> "A. Robinson" wrote:
>|||Ahh yes I see the flaw in my thinking.. The issue is that the batch or scrip
t
will treat the starting of the OSQL tool as being success and allow it to
move on to the next part of the script..
It doesn't matter if it call's itself via xp_cmdshell or if it is done
directly via the batch file.
hhhmmmmm
How about rewriting this as a vbscript?
You could use the scripts much the same way (build them and then open the
file and read each command in much the same way as the osql tool does now)
It might take a bit more work, but I think you can ensure that you get what
you want.
If you need an example of a script for such a purpose let me know (by
posting back)
Rob
"A. Robinson" wrote:
> THe first two scripts that generate SQL files aren't a problem. It's the
> scripts that perform DML. There are UPDATES and DELETES that take place an
d
> each script performs a ton of operations.
> The scripts end up stepping all over each other. There isn't a whole lot o
f
> need for debugging.
> Example: in the first script there is an ALTER done to a bunch of tables.
In
> the second script there is an ALTER done to a bunch of stored procedures t
hat
> are dependent on the table changes. If the second script runs before the
> first is complete, the second script will blow up because the table
> definitions will not match...
> "rob lynch" wrote:
>