Discussion:
OSQL
(too old to reply)
A. Robinson
2005-05-05 14:04:03 UTC
Permalink
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_PaidDate_Set.sql"
-o"E:\Documentation\CONVERSION
SCRIPTS\Script7-Conversion_Script_AR_ChargeRecord_PaidDate_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:
Jens Süßmeyer
2005-05-05 14:18:40 UTC
Permalink
YOu could use the Start [Command] /wait for it.

HTH, Jens Suessmeyer.

---
http://www-sqlserver2005.de
---
Post by A. Robinson
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_PaidDate_Set.sql"
-o"E:\Documentation\CONVERSION
SCRIPTS\Script7-Conversion_Script_AR_ChargeRecord_PaidDate_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"
A. Robinson
2005-05-05 14:28:04 UTC
Permalink
I don't see START as being an option for osql....

Could you give me an example?

Thanks!
Post by Jens Süßmeyer
YOu could use the Start [Command] /wait for it.
HTH, Jens Suessmeyer.
---
http://www-sqlserver2005.de
---
Post by A. Robinson
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_PaidDate_Set.sql"
-o"E:\Documentation\CONVERSION
SCRIPTS\Script7-Conversion_Script_AR_ChargeRecord_PaidDate_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"
Jens Süßmeyer
2005-05-05 14:55:24 UTC
Permalink
STart is a commandline util from DOS:

START OSQL -E -Q"Select GETDATE()" / wait

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---
Post by A. Robinson
I don't see START as being an option for osql....
Could you give me an example?
Thanks!
Post by Jens Süßmeyer
YOu could use the Start [Command] /wait for it.
HTH, Jens Suessmeyer.
---
http://www-sqlserver2005.de
---
Post by A. Robinson
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_PaidDate_Set.sql"
-o"E:\Documentation\CONVERSION
SCRIPTS\Script7-Conversion_Script_AR_ChargeRecord_PaidDate_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"
A. Robinson
2005-05-05 15:52:02 UTC
Permalink
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
Post by Jens Süßmeyer
START OSQL -E -Q"Select GETDATE()" / wait
HTH, Jens Suessmeyer.
---
http://www.sqlserver2005.de
---
Post by A. Robinson
I don't see START as being an option for osql....
Could you give me an example?
Thanks!
Post by Jens Süßmeyer
YOu could use the Start [Command] /wait for it.
HTH, Jens Suessmeyer.
---
http://www-sqlserver2005.de
---
Post by A. Robinson
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_PaidDate_Set.sql"
-o"E:\Documentation\CONVERSION
SCRIPTS\Script7-Conversion_Script_AR_ChargeRecord_PaidDate_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"
rob lynch
2005-05-05 17:31:12 UTC
Permalink
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..

:)
Post by A. Robinson
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
Post by Jens Süßmeyer
START OSQL -E -Q"Select GETDATE()" / wait
HTH, Jens Suessmeyer.
---
http://www.sqlserver2005.de
---
Post by A. Robinson
I don't see START as being an option for osql....
Could you give me an example?
Thanks!
Post by Jens Süßmeyer
YOu could use the Start [Command] /wait for it.
HTH, Jens Suessmeyer.
---
http://www-sqlserver2005.de
---
Post by A. Robinson
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_PaidDate_Set.sql"
-o"E:\Documentation\CONVERSION
SCRIPTS\Script7-Conversion_Script_AR_ChargeRecord_PaidDate_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"
A. Robinson
2005-05-05 17:55:20 UTC
Permalink
Rob:

I'd love to, but unfortunately Development isn't going to afford me such a
luxury...
Post by rob lynch
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..
:)
Post by A. Robinson
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
Post by Jens Süßmeyer
START OSQL -E -Q"Select GETDATE()" / wait
HTH, Jens Suessmeyer.
---
http://www.sqlserver2005.de
---
Post by A. Robinson
I don't see START as being an option for osql....
Could you give me an example?
Thanks!
Post by Jens Süßmeyer
YOu could use the Start [Command] /wait for it.
HTH, Jens Suessmeyer.
---
http://www-sqlserver2005.de
---
Post by A. Robinson
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_PaidDate_Set.sql"
-o"E:\Documentation\CONVERSION
SCRIPTS\Script7-Conversion_Script_AR_ChargeRecord_PaidDate_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"
rob lynch
2005-05-05 18:24:02 UTC
Permalink
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 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
Post by A. Robinson
I'd love to, but unfortunately Development isn't going to afford me such a
luxury...
Post by rob lynch
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..
:)
Post by A. Robinson
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
Post by Jens Süßmeyer
START OSQL -E -Q"Select GETDATE()" / wait
HTH, Jens Suessmeyer.
---
http://www.sqlserver2005.de
---
Post by A. Robinson
I don't see START as being an option for osql....
Could you give me an example?
Thanks!
Post by Jens Süßmeyer
YOu could use the Start [Command] /wait for it.
HTH, Jens Suessmeyer.
---
http://www-sqlserver2005.de
---
Post by A. Robinson
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_PaidDate_Set.sql"
-o"E:\Documentation\CONVERSION
SCRIPTS\Script7-Conversion_Script_AR_ChargeRecord_PaidDate_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"
A. Robinson
2005-05-05 18:42:05 UTC
Permalink
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 that
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...
Post by rob lynch
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 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
Post by A. Robinson
I'd love to, but unfortunately Development isn't going to afford me such a
luxury...
Post by rob lynch
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..
:)
Post by A. Robinson
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
Post by Jens Süßmeyer
START OSQL -E -Q"Select GETDATE()" / wait
HTH, Jens Suessmeyer.
---
http://www.sqlserver2005.de
---
Post by A. Robinson
I don't see START as being an option for osql....
Could you give me an example?
Thanks!
Post by Jens Süßmeyer
YOu could use the Start [Command] /wait for it.
HTH, Jens Suessmeyer.
---
http://www-sqlserver2005.de
---
Post by A. Robinson
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_PaidDate_Set.sql"
-o"E:\Documentation\CONVERSION
SCRIPTS\Script7-Conversion_Script_AR_ChargeRecord_PaidDate_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"
rob lynch
2005-05-05 18:56:02 UTC
Permalink
Ahh yes I see the flaw in my thinking.. The issue is that the batch or script
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
Post by A. Robinson
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 that
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...
Post by rob lynch
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 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
Post by A. Robinson
I'd love to, but unfortunately Development isn't going to afford me such a
luxury...
Post by rob lynch
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..
:)
Post by A. Robinson
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
Post by Jens Süßmeyer
START OSQL -E -Q"Select GETDATE()" / wait
HTH, Jens Suessmeyer.
---
http://www.sqlserver2005.de
---
Post by A. Robinson
I don't see START as being an option for osql....
Could you give me an example?
Thanks!
Post by Jens Süßmeyer
YOu could use the Start [Command] /wait for it.
HTH, Jens Suessmeyer.
---
http://www-sqlserver2005.de
---
Post by A. Robinson
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_PaidDate_Set.sql"
-o"E:\Documentation\CONVERSION
SCRIPTS\Script7-Conversion_Script_AR_ChargeRecord_PaidDate_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"
A. Robinson
2005-05-05 19:02:01 UTC
Permalink
Rob:

If you have an example that would be extremely cool!!

Thanks!
Post by rob lynch
Ahh yes I see the flaw in my thinking.. The issue is that the batch or script
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
Post by A. Robinson
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 that
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...
Post by rob lynch
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 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
Post by A. Robinson
I'd love to, but unfortunately Development isn't going to afford me such a
luxury...
Post by rob lynch
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..
:)
Post by A. Robinson
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
Post by Jens Süßmeyer
START OSQL -E -Q"Select GETDATE()" / wait
HTH, Jens Suessmeyer.
---
http://www.sqlserver2005.de
---
Post by A. Robinson
I don't see START as being an option for osql....
Could you give me an example?
Thanks!
Post by Jens Süßmeyer
YOu could use the Start [Command] /wait for it.
HTH, Jens Suessmeyer.
---
http://www-sqlserver2005.de
---
Post by A. Robinson
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_PaidDate_Set.sql"
-o"E:\Documentation\CONVERSION
SCRIPTS\Script7-Conversion_Script_AR_ChargeRecord_PaidDate_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"
rob lynch
2005-05-05 19:28:01 UTC
Permalink
This little scipt does something like what you want..
I use it is a setup routine..
The key method is the Run method of the Wscript.shell object. It has an
Optional third parameter of true if you want to wait for the command to
finish.

The execute doesn't do that.
In the case of the following script it waits till the restore has completed
before it does anything else. (3rd line - and you will see that I use OSQL
here also :)

It also goes on to create some stroedprocs (via a script and async
execution) and at the end builds a bunch of shortcuts on the desktop.

To run the script all you need to do is call it by name the same way as you
would a batch file (but you get better control by far!!!)

HTH

Rob


In a file called dosomething.vbs

Set WshShell = WScript.CreateObject("WScript.Shell")
wshShell.Run "Net Start MsSQLServer",1,True
WshShell.Run "osql -E -S. -id:\indot\RestoreDB.sql",1,True
wscript.sleep 2000
wshshell.PopUp "Check 'E:\databases' to confirm lanemessages.mdb is 8.75MB
",10,"Success"
WshShell.Exec "osql -E -S. -id:\install\systemstoredprocs.sql"
WshShell.Exec "d:\install\MoveFiles.bat"
WshShell.exec "d:\install\ChangePCName.exe"
BuildSHortcuts()
Function BuildShortCuts()
on error resume next
Set WshShell = WScript.CreateObject("WScript.Shell")

Set oShellLink =
WshShell.CreateShortcut("C:\winnt\profiles\Administrator.000\Desktop\Initialize System.lnk")
oShellLink.TargetPath = "D:\indot\SetupPrograms.vbs"
oShellLink.Save

Set oShellLink =
WshShell.CreateShortcut("C:\winnt\profiles\Administrator.000\Desktop\ChangePCName.lnk")
oShellLink.TargetPath = "D:\indot\ChangePCName.exe"
oShellLink.Save

Set oShellLink =
WshShell.CreateShortcut("C:\winnt\profiles\Administrator.000\Desktop\Event
Viewer.lnk")
oShellLink.TargetPath = "%SystemRoot%\system32\eventvwr.exe"
oShellLink.Save
wshshell.popup "ShortCuts Created!",10
set WshShell = nothing
End Function

Continue reading on narkive:
Loading...