title: Find Job_State of SQL Agent Job - SQL Server
body: <p>I am using below script to get the Job_state for Agent Job.</p>
  
  <p>It returns 1 as job_state when job is running => which is fine.</p>
  
  <p>But for all other cases (failed / success / cancelled) it returns job_status 4 (which means its idle)</p>
  
  <p>How can I get exact job_state so that I can determine whether it is failed / success or cancelled.</p>
  
  <p>My script so far:</p>
  
  <pre><code>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
  </code></pre>