Find Job_State of SQL Agent Job - SQL Server

Question

I am using below script to get the Job_state for Agent Job.

It returns 1 as job_state when job is running => which is fine.

But for all other cases (failed / success / cancelled) it returns job_status 4 (which means its idle)

How can I get exact job_state so that I can determine whether it is failed / success or cancelled.

My script so far:

DECLARE @xp_results TABLE
(
  job_id UNIQUEIDENTIFIER NOT NULL ,
  last_run_date INT NOT NULL ,
  last_run_time INT NOT NULL ,
  next_run_date INT NOT NULL ,
  next_run_time INT NOT NULL ,
  next_run_schedule_id INT NOT NULL ,
  requested_to_run INT NOT NULL ,
  request_source INT NOT NULL ,
  request_source_id SYSNAME COLLATE database_default NULL ,
  running INT NOT NULL ,
  current_step INT NOT NULL ,
  current_retry_attempt INT NOT NULL ,
  job_state INT NOT NULL
)      
SET NOCOUNT ON
INSERT  INTO @xp_results
    EXEC master.dbo.xp_sqlagent_enum_jobs 1, ''

DECLARE @jobs TABLE
(
  rownum INT IDENTITY(1, 1)
             PRIMARY KEY
             NOT NULL ,
  job_id UNIQUEIDENTIFIER NOT NULL
)

SELECT  name,job_state
FROM    @xp_results rj
    INNER JOIN msdb.dbo.sysjobs sj ON sj.job_id = rj.job_id

Answer 1

I got this resolved as below. There should be other good way to do it.

DECLARE @xp_results TABLE
(
  job_id UNIQUEIDENTIFIER NOT NULL ,
  last_run_date INT NOT NULL ,
  last_run_time INT NOT NULL ,
  next_run_date INT NOT NULL ,
  next_run_time INT NOT NULL ,
  next_run_schedule_id INT NOT NULL ,
  requested_to_run INT NOT NULL ,
  request_source INT NOT NULL ,
  request_source_id SYSNAME COLLATE database_default NULL ,
  running INT NOT NULL ,
  current_step INT NOT NULL ,
  current_retry_attempt INT NOT NULL ,
  job_state INT NOT NULL
)      
SET NOCOUNT ON
INSERT  INTO @xp_results
    EXEC master.dbo.xp_sqlagent_enum_jobs 1, ''

DECLARE @jobs TABLE
(
  rownum INT IDENTITY(1, 1)
             PRIMARY KEY
             NOT NULL ,
  job_id UNIQUEIDENTIFIER NOT NULL
)
DROP TABLE #temp    
exec sp_serveroption 'hts0519', 'data access', 'true'
select * 
INTO #temp
FROM OPENQUERY( hts0519, ' EXEC msdb.dbo.sp_help_job')

SELECT  sj.name,

    CASE job_state WHEN 4 THEN 'IDLE' ELSE 'RUNNING' END AS CURRENT_STATUS, 
    CASE tmp.last_run_outcome
        WHEN 0 THEN 'FAILED'
        WHEN 1 THEN 'SUCCEEDED'
        WHEN 3 THEN 'CANCELED'
        WHEN 5 THEN 'UNKNOWN'   
    END  AS LAST_OUTCOME
 FROM    @xp_results rj
    INNER JOIN msdb.dbo.sysjobs sj ON sj.job_id = rj.job_id
    INNER JOIN #temp tmp ON sj.name = tmp.name