if object_Id( 'dbspDBARpt_ChkJobHistory') Is Not Null
 drop procedure dbspDBARpt_ChkJobHistory
go

CREATE procedure dbspDBARpt_ChkJobHistory
as
/*
*************************************************************
Name: dbspDBARpt_ChkJobHistory
Description:
   Check for jobs that have failed or the run status is unknown.
The history is checked for all days since the last check.

Usage:exec dbspDBARpt_ChkJobHistory

Author: Steve Jones

Input Params:
-------------

Output Params:
--------------

Return:

Results:
---------

Locals:
--------
@err		Holds error value

Modifications:
--------------

*************************************************************
*/
set nocount on
declare @err int,
		@lastdt datetime,
		@intdt int,
		@txt char( 80),
		@typ char( 20),
		@db char( 40),
		@cmd varchar( 500), @tbl char( 40)

select @err = 0
/*
Check parameters and exit if not correct.
*/
if @err = -1
 begin
  Raiserror( 'Parameter Error:Usage:exec dbspDBARpt_DBALog', 12, 1)
  return @err
 end

select @typ = 'Job History'

insert DBARpt ( Srvr, Typ, txt, entrydt) values ( @@servername, @typ, ' ', getdate())

select @txt = 'Failed Job History Report'
insert DBARpt ( Srvr, Typ, txt, entrydt) values ( @@servername, @typ, @txt, getdate())
select @txt = '---------------------------------------------------------'
insert DBARpt ( Srvr, Typ, txt, entrydt) values ( @@servername, @typ, @txt, getdate())

create table #jobhist
 ( jobid 	uniqueidentifier,
	job_name	sysname,
	run_status int,
	run_date int,
	run_time int,
	run_duration int,
	operator_emailed varchar( 20),
	operator_netsend varchar( 20),
	operator_paged varchar( 20),
	retries_attempted int,
	server varchar( 30)
 )

select @lastdt = lastxfr
	from datafeedtrckr df
 	where df.tblname = 'dbspDBARpt_ChkJobHistory'

select @intdt = ( datepart( year, @lastdt) * 10000) + ( datepart( month, @lastdt) * 100) + datepart( day, @lastdt)
insert #jobhist
	exec msdb..sp_help_Jobhistory @run_status = 0, @start_run_date = @intdt, @mode = 'Summary'
insert #jobhist
	exec msdb..sp_help_Jobhistory @run_status = 5, @start_run_date = @intdt, @mode = 'Summary'

insert dbarpt
 select @@servername,
	'Failed Jobs',
	getdate(),
	rtrim( job_name) + ' run on '+ cast( run_date as char( 8)) + 
		':' + cast( run_time as char( 6)) + 
		' Retry:' + cast( retries_attempted as char( 2)) +
		' Status:' + case 
							when run_status = 0 
								then 'Failed'
							when run_status = 5
								then 'Unknown'
						end
 from #jobhist

/*
Update history of the data transfer
*/
update datafeedtrckr
	set lastxfr = getdate()
	where tblname = 'dbspDBARpt_ChkJobHistory'

/*
Clean up 
*/
drop table #jobhist

return @err
GO
grant execute on dbspDBARpt_ChkJobHistory to WebUser
go
if object_id( 'dbspDBARpt_ChkJobHistory') Is Null
 select 'Error:dbspDBARpt_ChkJobHistory Not Created'
else
 select 'dbspDBARpt_ChkJobHistory Created'
go