CREATE procedure dbo.P_ANY_JOB_STEP_FAIL
	@recipients varchar(100) ,
	@hours_back int = -12
as

begin

SET NOCOUNT ON
 
declare @subject varchar(50),@message varchar(1000), @minid int,@maxid int, 
	@dt datetime,@dti bigint, @min_run_time int, @max_run_time int

select @dt = DATEADD(hh, @hours_back , getdate())

select @subject = 'Jobs fail today: ' + cast(@dt as varchar)

select @dti = CAST( cast(datepart(year,@dt) as varchar) + 
				right('00' + cast(datepart(mm,@dt) as varchar), 2) + 
				right('00' + cast(datepart(dd,@dt) as varchar), 2) + 
				right('00' + cast(datepart(hh,@dt) as varchar), 2) +
				right('00' + cast(datepart(mi,@dt) as varchar), 2) + 
				right('00' + cast(datepart(ss,@dt) as varchar), 2)  as bigint)
				
create table #tmp(job_name varchar(255),step_name varchar(255),run_status int, 
		run_date int, run_time int, ident_id int identity(1,1))

insert into #tmp(job_name,step_name)
select distinct left(sj.name,60) ,left(sos.step_name,60)  
 from msdb..sysjobs sj 
	inner join msdb..sysjobsteps sos on sos.job_id = sj.job_id 
	inner join msdb..sysjobhistory soh on soh.job_id = sos.job_id and soh.step_id = sos.step_id
where 	soh.run_status = 0 
	and cast( 
		cast(soh.run_date as varchar(8))  + right('00' + cast(soh.run_time as varchar) , 6) as bigint) >= @dti 
	
select @minid = min(ident_id),@maxid = max(ident_id) from #tmp

select @message = IsNull(@@SERVERNAME, '') + '. Failed :  
'

If ( (select count(*) from #tmp) > 0)
	begin
		while (@minid <= @maxid)
			begin

				select @message = @message + 'Job name: ' + job_name + '. Step name: ' + step_name + '; 
' 
				from #tmp 
				where ident_id = @minid

				select @minid = @minid + 1
			end

	exec master..xp_sendmail 
		@recipients = @recipients, 
		@subject = @subject,  
		@message = @message
	end
end