Failed Job Report

USE [msdb]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

ALTER PROCEDURE [dbo].[dba_sp_FailedJob]

AS

CREATE TABLE #Temp (ServerName varchar(100) NULL, JobName varchar(500) NULL, StepID int NULL, StepName varchar(500) NULL, DatabaseName varchar(500) NULL, LastRunDate int NULL, RoleDescription varchar(50) NULL, SortOrder int NULL)

DECLARE @SQL varchar(max), @InstanceName varchar(max), @RoleDescription varchar(max), @SortOrder int

DECLARE FailedJob CURSOR FOR

SELECT DISTINCT InstanceName, RoleDescription, SortOrder FROM dba_ServerName



OPEN FailedJob

FETCH NEXT FROM FailedJob INTO @InstanceName, @RoleDescription, @SortOrder

WHILE @@FETCH_STATUS = 0 AND @@ERROR = 0

BEGIN

/*

SET @sql = 'select distinct j.name JobName,s.step_id, s.step_name, s.database_name, s.last_run_date

from ' + '[' + @InstanceName + '].' + 'msdb.dbo.sysjobsteps s inner join ' + '[' + @InstanceName + '].' + 'msdb.dbo.sysjobs j on s.job_id=j.job_id

where s.last_run_outcome=0

and s.last_run_date<>0

and j.enabled=1'

*/

SET @sql = 'SELECT a.*,' + '''' + @RoleDescription + '''' + ', ' + '''' + cast(@SortOrder as varchar) + '''' + ' FROM OPENROWSET(' + '''' + 'SQLNCLI' + '''' + ', '

+ '''' + 'Server=' + @InstanceName + ';Trusted_Connection=yes;' + ''''

+' ,'

+ '''' + ' select distinct @@ServerName as Server, j.name JobName,s.step_id, s.step_name, s.database_name, s.last_run_date

from msdb..sysjobsteps s inner join msdb..sysjobs j on s.job_id=j.job_id

where s.last_run_outcome=0

and s.last_run_date<>0 and s.last_run_date = (select max(last_run_date) from msdb..sysjobsteps s2 where s2.job_id = s.job_id)

and j.enabled=1' + ''''

+ ') AS a;'



PRINT @sql

INSERT INTO #Temp

EXEC(@sql)



FETCH NEXT FROM FailedJob INTO @InstanceName, @RoleDescription, @SortOrder

END

CLOSE FailedJob

DEALLOCATE FailedJob

SELECT * FROM #Temp

-- Send Email

DECLARE @HTML varchar(max), @subj varchar(500), @Descr varchar(500)

if exists(select * from #Temp)

begin

set @subj='Failed Job Report for ' + convert(varchar, getdate(), 1) + ':Bad'

set @Descr = (SELECT CAST(count(*) as varchar) from #Temp) + ' failed jobs'

end

else

begin

set @subj='Failed Job Report for ' + convert(varchar, getdate(), 1) + ':Good'

set @Descr = 'No failed jobs'

end

SET @HTML =

N'<html><head><style>

body{color: white ;font: 8pt Arial;}

h5{color: navy }

table{font: 8pt "Arial";color: margin: 1em auto;border-collapse: collapse;white; width: 100%}

th{color: #ffffff; background-color:#808080}

tr{color: navy; background-color:#ffffff}

#celSlide{letter-spacing: 10;color: white;}

</style></head>' +

--N'<H5><FONT COLOR=Red>Failed job report for ' + convert(varchar, getdate(), 1) + '</FONT></H4>' +

N'<H5><FONT COLOR=Red>' + @Descr + '</FONT></H4>' +

N'<table border="1">' +

N'<th>Server</th><th>Role</th><th>Job</th><th>StepID</th><th>Step</th><th>Database</th><th>LastRunDate</th>' +

CAST ( ( SELECT td = ISNULL(ServerName, ''), '',

td = ISNULL(RoleDescription, ''), '',

td = ISNULL(JobName, ''), '',

td = ISNULL(StepID, ''), '',

td = ISNULL(StepName, ''), '',

td = ISNULL(DatabaseName, ''), '',

td = convert(varchar(10),substring(convert(varchar(10),LastRunDate),5,2)+ '/' + substring(convert(varchar(10),LastRunDate),7,2)) + '/'+ substring(convert(varchar(10),LastRunDate),1,4)

FROM #Temp

ORDER BY SortOrder, ServerName, JobName FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'</table>' ;

exec msdb.dbo.sp_send_dbmail @profile_name='EmailProfileName',

@recipients='EmailID1, EmailID2;',

@subject = @subj,

@body = @html,

@body_format = 'HTML'

DROP TABLE #Temp