SQL Job failure

This script identifies failure of sql jobs to the connected sql instance.

USE [master]
GO
/****** Object: StoredProcedure [dbo].[up_sqljob_check] Script Date: 08/22/2009 09:22:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[up_sqljob_check]
as
begin
-- Variable Declarations
DECLARE @PreviousDate datetime
DECLARE @Year VARCHAR(4)
DECLARE @Month VARCHAR(2)
DECLARE @MonthPre VARCHAR(2)
DECLARE @Day VARCHAR(2)
DECLARE @DayPre VARCHAR(2)
DECLARE @FinalDate INT

-- Initialize Variables
SET @PreviousDate = DATEADD(dd, -1, GETDATE()) -- Last 7 days
--select @PreviousDate
SET @Year = DATEPART(yyyy, @PreviousDate)
SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))
SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)
SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))
SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2)
SET @FinalDate = CAST(@Year + @Month + @Day AS INT)
--select @FinalDate
-- Final Logic
/*
SELECT j.[name],
s.step_name,
h.step_id,
h.step_name,
h.run_date,
h.run_time,
h.sql_severity,
h.message,
h.server
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j
ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id
AND h.step_id = s.step_id
WHERE h.run_status = 0 -- Failure
AND h.run_date > @FinalDate
and enabled = '1'
ORDER BY h.instance_id DESC
*/
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML = N'

SQL Job Failures on 34 server

' +
N' ' +
N' '+
-- N' ' +
N'' +
CAST ( ( select distinct
'2' "td/FONT/@Size", j.name "td", ''
-- '2' "td/FONT/@Size", h.message "td", ''
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j
ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id
AND h.step_id = s.step_id
WHERE h.run_status = 0 -- Failure
AND h.run_date > @FinalDate
and enabled = '1'
--ORDER BY h.instance_id DESC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'

Name	Message

' ;
declare @count numeric
SELECT @count = count( distinct j.[name] )
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j
ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id
AND h.step_id = s.step_id
WHERE h.run_status = 0 -- Failure
AND h.run_date > @FinalDate
and enabled = '1'
select @count
if @count > 1
begin
EXEC msdb.dbo.sp_send_dbmail
@recipients = '',
--@copy_recipients = '',
@subject = ' SQL Job failures on 34 server',
@body = @tableHTML,
@body_format = 'HTML' ;
end
end
--select * from MSDB.dbo.sysjobs