Post reply

call sql agent job in SQL or batch file and need return job failure or success status

  • September 14, 2011 at 8:30 pm

    #243838

    We have to use a batch file that we had a 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'"

    It always returns 0 because the job started successfully,but I would like to catch the return code in the batch file if the sql agent job succeed, it returns 0 , else returns 1.

    Is there a way to do that?

    The batch file can also call a sql file, but still need to return error code.

    The batch file eventally will return the error code to the program it is called from

    Thanks

    Edited: Today @ 3:18 AM by sqlfriends

  • September 14, 2011 at 8:46 pm

    #1381706

    After starting the job you will want to run a loop which calls sp_help_job and passing in the job name. This will tell you if the job is running is. Once it's done you can call sp_help_jobhistory to get the output for the last run of the job to see if it was successful.

    Both of these stored procedures can be found in the msdb database and their usage is documented in Books OnLine.

  • September 15, 2011 at 2:50 am

    #1381759

    Thanks, is there any sample script about this?

    Also how in each step to return the suceed or failure status:

    1. batch file to call a sql script, something like this in batch file:

    SQLCMD -i "c:/myfolder/myfile.sql'

    2. in the sql script do the following and also return the status to the batch file, how can i do that?

    EXEC msdb.dbo.sp_start_job @job_name='transfer data'

    How in each step to return status to the calling one?

    Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply