SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

use DBA
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_get_job_status]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_get_job_status]
GO



CREATE     PROCEDURE usp_get_job_status
/*
Author: Jon Baker
Date : 7/25/2005
Paramaters : None

Test Data :
usp_get_job_status
select * from dba.dbo.dba_job_status


Purpose : 	To collect data for daily monitoring of job status


Revisions :

*/
AS

set nocount on

DECLARE @rc int
DECLARE @errmsg varchar (256)


--If the logging table does not exist, create it
if not exists (select 1 from dba.dbo.sysobjects where xtype = 'u' and name = 'dba_job_status')
BEGIN

	CREATE TABLE dbo.dba_job_status
	(
		collect_time datetime not null,
		server_name sysname not null,
		instance_id int not null,
		job_name sysname null,
		step_name sysname null,
		message nvarchar(2048) null,
		run_date int null,
		run_time int null,
		run_duration int null,
		CONSTRAINT PK_dba_job_status PRIMARY KEY (collect_time desc, instance_id desc)
		
	)
	if @@error <> 0 
		BEGIN
			set @errmsg = 'create table failed!'
			goto ERRORPROC
		END	
END

declare @now datetime
set @now = getdate()
declare @max_instance_id int
set @max_instance_id = isnull((select max(instance_id) from dbo.dba_job_status),0)

INSERT INTO [DBA].[dbo].[dba_job_status]
(
		[collect_time],
		[server_name],
		[instance_id],
		[job_name],
		[step_name],
		[message],
		[run_date],
		[run_time],
		[run_duration]
)
SELECT 
 @now, @@servername,
 H.instance_id, J.name, H.step_name,
 H.message, H.run_date, H.run_time, H.run_duration
 from msdb.dbo.sysjobs J 
 join msdb.dbo.sysjobhistory H
 on J.job_id=H.job_id
 where H.run_status=0 and H.instance_id>@max_instance_id
 order by H.instance_id desc


if @@error <> 0 
		BEGIN
			set @errmsg = 'INSERT failed!'
			goto ERRORPROC
		END
ELSE 
	goto ENDPROC

ERRORPROC:
	BEGIN
		
		RAISERROR (@errmsg, 16, 1)	
		RETURN (-1)
	END


ENDPROC:
	BEGIN
		Return (0)
	END