CJSmith dot me

I dump stuff I find useful here

Simple error checking when running batch files from T-SQL in SQL Server

Published / by Chris Smith / Leave a Comment

A while ago we added error trapping to the batch file called by a SQL script, this was to ensure if a program failed the rest of the process would not run:

After ensuring the program would return an error level if something went wrong we then I then used the ErrorLevel and an if statement. If the error level is equal to or greater than 1, I make it go to the end of the file and exit with code 1 (By default its zero which normally means the batch or program executed as expected)

rem Batch Code here must support returning errors otherwise it will be assumed to have run without issue
echo Error Level %ERRORLEVEL%
rem add any further Batch Code here
echo Error Level 0 so closing
echo Reached End of file due to program error
exit /b 1

We then modified our existing code in SQL to ensure the batch program is run and the exit code is captured in a variable. It can then be dealt with and the example below can easily be changed to suit whatever is required.

print 'Running Windows Batch Script'
--Capture the exit code into @result and run the batch script
DECLARE @result int
       EXEC @result = xp_cmdshell '<Patch_to_Batch_Script>'
--Only one command can be run in this if statement if more than one required consider a GOTO command!
              IF (@result = 0)
--Run if program ran fine
                     EXEC xp_cmdshell '<Patch_to_Batch_Script>'
--Run if program did not run fine
                     ELSE '<Patch_to_Batch_Script>'

After a few issues with some scripts I have stopped using Else in favour of the not equaling 0

print @result
   IF @result = 0
                IF @result != 0

Microsoft MSDN (Example Set C)

Leave a Reply