USE msdb

SELECT

  sysjobhistory.run_status

FROM

  sysjobhistory

  INNER JOIN sysjobs ON 

  sysjobhistory.job_id = sysjobs.job_id

WHERE

  (sysjobs.name = 'job_name')

[/src]

0 Returns only those jobs that are not idle or suspended.  

1 Executing. 

2 Waiting for thread. 

3 Between retries. 

4 Idle. 

5 Suspended. 

7 Performing completion actions. 

[src]

Status of the job execution: 

0 = Failed

1 = Succeeded

2 = Retry

3 = Canceled

4 = In progress

if object_id('tempdb..#t') is not null drop table #t

create table #t(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 nvarchar(200) ,

running int,

current_execution_step int ,

current_retry_attempt  int ,

current_execution_status  int )



insert #t EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, N''

select * 

from #t a

inner join msdb.dbo.sysjobs_view b on b.job_id = a.job_id



drop table #t

[/src]

CREATE       PROCEDURE dbo.adm_job_status 

	@job_name 	sysname 	 = NULL	OUTPUT,

	@job_id 	UNIQUEIDENTIFIER = NULL	OUTPUT,

	@CurrentJobStatus int OUTPUT

AS



SET NOCOUNT ON



DECLARE @object int

DECLARE @hr int

DECLARE @ret int

DECLARE @src varchar(255)

DECLARE @desc varchar(255)

DECLARE @job_str_id sysname



IF @job_name IS NOT NULL

BEGIN

	EXECUTE msdb.dbo.sp_verify_job_identifiers '@job_name',

	                                        '@job_id',

	                                         @job_name OUTPUT,

	                                         @job_id   OUTPUT

END



SET @job_str_id = @job_id



EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT

IF @hr <> 0

BEGIN

	EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT 

	SELECT  hr=convert(varbinary(4),@hr), Source=@src, Description=@desc

	RETURN

END



DECLARE @LoginSecure bit

SET @LoginSecure = 1

EXEC @hr = sp_OASetProperty @object, 'LoginSecure', @LoginSecure

IF @hr <> 0

BEGIN

	EXEC sp_OAGetErrorInfo @object

	RETURN

END



EXEC @hr = sp_OAMethod @object OUTPUT, 'Connect', NULL, 'localhost'



IF @hr <> 0

BEGIN

	EXEC sp_OAGetErrorInfo @object

	RETURN

END



DECLARE @JobServer int

EXEC @hr = sp_OAGetProperty @object, 'JobServer', @JobServer OUT

IF @hr <> 0

BEGIN

	EXEC sp_OAGetErrorInfo @JobServer

	RETURN

END



DECLARE @Job int

EXEC @hr = sp_OAMethod @JobServer OUTPUT, 'GetJobByID', @Job OUTPUT, @job_str_id



IF @hr <> 0

BEGIN

	EXEC sp_OAGetErrorInfo @JobServer

	RETURN

END



EXEC @hr = sp_OAGetProperty @Job, 'CurrentRunStatus', @CurrentJobStatus OUT

IF @hr <> 0

BEGIN

	EXEC sp_OAGetErrorInfo @Job

	RETURN

END



EXEC @hr = sp_OAMethod @object OUTPUT, 'Close', NULL

IF @hr <> 0

BEGIN

	EXEC sp_OAGetErrorInfo @object

	RETURN

END



EXEC @hr = sp_OADestroy @object

IF @hr <> 0

BEGIN

	EXEC sp_OAGetErrorInfo @object

	RETURN

END



[src]