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:
>