Master.dbo.xp_sqlagent_enum_jobs - how to get the result of the last run

Question

How can I get the result of the last run using master.dbo.xp_sqlagent_enum_jobs

CREATE TABLE #enum_job
(
  Job_ID UNIQUEIDENTIFIER,
  Last_Run_Date INT,
  Last_Run_Time INT,
  Next_Run_Date INT,
  Next_Run_Time INT,
  Next_Run_Schedule_ID INT,
  Requested_To_Run INT,
  Request_Source INT,
  Request_Source_ID VARCHAR(100),
  Running INT,
  Current_Step INT,
  Current_Retry_Attempt INT,
  State INT
)
INSERT  INTO #enum_job
    EXEC master.dbo.xp_sqlagent_enum_jobs 1, garbage
SELECT  *
FROM    #enum_job

Answer 1

Your question is a bit unclear, and you don’t say which version of MSSQL you have, but provided that you want to find the latest job result for each job, you can simply query the job tables directly:

select 
    j.name as 'Job', jh.run_status as 'Result of last run'
from 
    msdb.dbo.sysjobs j
    join msdb.dbo.sysjobhistory jh
    on j.job_id = jh.job_id
where 
    jh.step_id = 0 and
    jh.run_date = (select max(run_date) from msdb.dbo.sysjobhistory where job_id = jh.job_id) and
    jh.run_time = (select max(run_time) from msdb.dbo.sysjobhistory where job_id = jh.job_id and run_date = jh.run_date)

. sysjobhistory run_status. , , , (run_date, run_time) datetime. , TSQL , , Smo.