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%
IF ERRORLEVEL GEQ 1 GOTO EOF
rem add any further Batch Code here
echo Error Level 0 so closing
exit
:EOF
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
BEGIN
code
END
IF @result != 0
BEGIN
code
END
Credits:
SS64
Microsoft MSDN (Example Set C)
