December 22, 2004 at 7:24 am
SQL2000 SP3. I have a large number of SQL scripts to run against one particular database on one particular server....
I'm looking to automate the whole thing with a batch file, yet have to exit if there is an error.
What I have so far is this:
I created a batch file called batchfile.bat which contains the following:
ECHO SET CONCAT_NULL_YIELDS_NULL ON >> temp.sql
ECHO set quoted_identifier off >>temp.sql
ECHO set transaction isolation level read uncommitted >> temp.sql
ECHO use %2 >> temp.sql
ECHO GO >> temp.sql
type %3 >> temp.sql
OSQL -S %1 -d %2 -i temp.sql -b -n -E > %3.out
--------
When I run the batch file, it goes like this....
batchfile.bat servername databasename scriptname.sql
Now I have another batch file that contains the above line many time but with the scriptname.sql different for each file storedproc\script I need to run.
What I'm looing for is a way to error out the main batch file completely if any error occurs....I want the current OSQL statement to quit AND I don't want the batch file to continue to the next batchfile.bat line....I'm sure it's some easy FOR statement or something like that but my batch programming skills aren't quite up to par....
Any help would be appreciated.
December 23, 2004 at 2:34 am
I used to use OSQL but was very slow and cumbersome...
I now use a modified version of this one:
http://msdn.microsoft.com/msdnmag/issues/04/09/CustomDatabaseInstaller/default.aspx
I would recommend using this method to deploy and it can be optionally run in batch mode.
Cheers
Michael
December 23, 2004 at 6:50 am
Add
-m-1
to your osql parameters
Create your batch file like this
CALL batchfile.bat servername databasename scriptname1.sql
IF ERRORLEVEL 1 GOTO ER
CALL batchfile.bat servername databasename scriptname2.sql
IF ERRORLEVEL 1 GOTO ER
CALL batchfile.bat servername databasename scriptname3.sql
IF ERRORLEVEL 1 GOTO ER
GOTO EX
:ER
ECHO Error
:EX
Far away is close at hand in the images of elsewhere.
Anon.
December 23, 2004 at 7:05 am
Great!! Thanks, that seems like exactly what I'm looking for. I'll give it a shot.
December 23, 2004 at 7:08 am
Actually, one last question...I believe that only the "output" of an sql file will be put in to the output file...How would I capture the error if one is returned?
Thanks!
December 23, 2004 at 7:24 am
All output, errors as well are put in the output, whether redirected or if using the -o parameter.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply