Return a status or value from sp_start_job
In my procedure, I’m calling a job on a remote server and if this call fails, I would like the code to handle it accordingly. So if I get a return value or status of 0, the job started successfully. If 1, then it failed to start.
Now I can’t monitor the entire duration of the SQL job on the remote server, I’ll need another process for it.
The code to return a value or status from sp_start_job is as follow:
declare @rnt int
exec @rnt = msdb.dbo.sp_start_job @job_name = ‘JOB_NAME’
if @rnt = 1
begin
— code to handle the failure
end