Using sp_help_job from within a TSQL procedure

• Nili S

  Right there with Babe

  Points: 795

  More actions

  May 10, 2007 at 1:03 am

  #124041

  Hello.

  Can anyone please help me with an example code of how I get a job's execution status using sp_help_job, but from within a TSQL procedure? (How do I read a specific data from the function? it returns a huge ResultSet- How do I read the execution_status and use it?)

  Also- is the try/catch block suppose to catch SQLServer Agent errors? (Job errors- such as "Cannot stop job ***- job is not running")

  Thank you

• Greg Charles

  SSC-Forever

  Points: 45403

  More actions

  May 10, 2007 at 10:18 am

  #705561

  You can create a temporary table in the stored procedure that has columns for the results of sp_help_job.  Then, insert the results of sp_help_job into the temp table.  You can then select current_execution_status from the temp table.

  Something like:

  create table #jobinfo (job_id  uniqueidentifier,

                                originating_server nvarchar (30),

                                .

                                .

                                )

  insert #jobinfo exec sp_help_job

  select current_execution_status

  from #jobinfo

   

  Greg

   

  Greg