September 14, 2011 at 4:55 pm
I have a third party schedule program that to call a batch file that we use sqlcmd to start a sql agent job , in the batch file we use:
SQLCMD -Q "EXEC msdb.dbo.sp_start_job @job_name='TRANSFER_DATA'"
My question is : it always return 0 because the job started successfully, even later the job runs and failed, so the third part program sees it succeed but in sql agent job history it failed.
Is there a way that we can write something in the batch file to use if sql agent job failed, then it returns the error code to the batch file?
Thanks
September 15, 2011 at 10:11 am
You would have to do something in the batch file that continually checks the status of the specific job until it gets a Success or Failure message for it. You can use sp_help_job to get this information. You can look at last run outcome in the final result set returned.
There is probably another query you could use as well, but this will get you started.
You have to do this because the only thing the batch from SQLCMD is doing is starting the job, so if it starts that is success.
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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply