Capture Job Failed/Success Status

• AVB

  SSChampion

  Points: 11418

  More actions

  March 24, 2009 at 9:30 am

  #202558

  I am calling a job using sp_Start_Job and I want return a failed or successful bit.

  How can I tell if the job errors? I'd like to return a simple 1 or 0 as an output in the proc.

  Create Proc JobStart

  @Error tinyint out

  as

  EXEC msdb.dbo.sp_start_job @job_name = 'Import CSV'

  Something to the effect of:

  IF job fails set @Error = 2

  If success set @Errror = 1

  TIA.

• Greg Charles

  SSC-Forever

  Points: 45403

  More actions

  March 24, 2009 at 12:25 pm

  #965489

  Since sp_start_job runs asynchronously, it won't wait for the job to finish without you doing some work in the stored procedure. There's a post in this thread that gives an idea for making it wait: http://www.sqlservercentral.com/Forums/Topic156802-5-1.aspx#bm156834.

  I haven't tried this myself, but you could try searching the script section for something.

  Greg