May 3, 2016 at 7:53 am
I am preparing a report to generate failed jobs into an email. This is the script I am using
DECLARE @tableHTMLA NVARCHAR(MAX) ;
SET @tableHTMLA =
N'<font face="Arial" size="+1" COLOR="#7AA9DD">Failed jobs in the last 10 days</font>' +
N'<table border="0" cellpadding="3">' +
N'<tr bgcolor=#D9D9D9><th><font face="Arial" size="-1">Job Name</font></th>' +
N'<th><font face="Arial" size="-1">Run Date</font></th>' +
N'<th><font face="Arial" size="-1">Run Time</font></th><th><font face="Arial" size="-1">Error Message</font></th><th><font face="Arial" size="-1">Server</font></th>' +
CAST ( ( SELECT j.name AS 'td','',h.run_date AS 'td','',h.run_time AS 'td','',
h.message AS 'td','',h.server AS '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
ORDER BY h.instance_id DESC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Mail profile',
@recipients = 'abc@gmail.com,
@subject = 'Job Status Report',
@body = @TableHtmlA,
@body_format = 'HTML' ;
I have this script..it works fine when there are any failed jobs...
when there are no failed jobs it just gives a blank report. I want to display a message 'No failed jobs reported' instead of a blank report.
any ideas would be greatly appreciated.
Thanks
May 3, 2016 at 8:02 am
Maybe can use this after your query:
If @@rowcount = 0
Begin
SET @tableHTMLA = 'No failed jobs reported'
End
May 3, 2016 at 8:53 am
That was so simple. Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply