November 12, 2015 at 4:09 am
I am using below batch file to trigger the sql server job ABC.Job ABC is triggered and failed it should go to echo statement THERE WAS AN ERROR but control is going to Echo sql server job completed successfully even though there was an error.Any help is appreciated
REM This is a batch file to trigget the sql server job
@echo off
echo Go to Bin Directory
cd C:\Program Files\Microsoft SQL Server\110\Tools\Binn
echo Start the SQL Server Job
sqlcmd -S xxxxxx -U xxxxxx -P xxxxxx -b -Q "exec msdb.dbo.sp_start_job @job_name = N'ABC'"
IF %ERRORLEVEL% ==1 (
Echo THERE WAS AN ERROR
) ELSE (
Echo sql server job completed successfully.
)
November 12, 2015 at 12:10 pm
You need to add the -b option to get sqlcmd to return a DOS ERRORLEVEL. From BOL for SQLCMD:
-b on error batch abort
Specifies that sqlcmd exits and returns a DOS ERRORLEVEL value when an error occurs. The value that is returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity level greater than 10; otherwise, the value returned is 0. If the -V option has been set in addition to -b, sqlcmd will not report an error if the severity level is lower than the values set using -V. Command prompt batch files can test the value of ERRORLEVEL and handle the error appropriately. sqlcmd does not report errors for severity level 10 (informational messages).
If the sqlcmd script contains an incorrect comment, syntax error, or is missing a scripting variable, ERRORLEVEL returned is 1.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 13, 2015 at 2:58 am
I have already Included -b in the query.Please see my original post but still it is not working
November 13, 2015 at 3:44 am
All you're doing is running sp_start_job, which is presumably successful, and therefore you don't get an error.
It doesn't then hang around for the job to complete, so if it fails, you will never know in that script.
December 15, 2015 at 6:28 am
Check the -Q and -b special behavior when together...
If -b is specified together with this option (-Q), sqlcmd exits on error. -b is described later in this topic.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply