May 3, 2014 at 3:16 am
Comments posted to this topic are about the item Email Failed SQL Jobs
May 28, 2014 at 4:21 am
I changed the code and made it a stored procedure in my dba database.
I also added a filter to list only enabled jobs.
if object_id('dbo.usp_mailFailedJobs') is null exec('create procedure dbo.usp_mailFailedJobs as begin;return;end;')
go
alter procedure dbo.usp_mailFailedJobs
(
@recipients varchar(max)
,@profile_name sysname = null
,@subject nvarchar(255) = @@servername
) as
begin
set nocount on;
declare @body nvarchar(max) = cast
(
(
select
J.name as 'td',''
,S.last_run_outcome as 'td',''
,S.last_outcome_message as 'td',''
,S.last_run_date as 'td',''
from msdb.dbo.sysjobs as J
join msdb.dbo.sysjobservers as S on S.job_id = J.job_id
where S.last_outcome_message like '%failed%'
and J.enabled = 1
for xml path('tr'),type
)
as nvarchar(max)
);
if @body is not null
begin;
set @body = concat
(
N'<html><body><H3>Failed jobs</H3>'
,N'<table border="1">'
,N'<tr><th>Job Name</th><th>Last Run Outcome</th><th>Last Outcome Message</th><th>Last Run Date</th></tr>'
,@body
,N'</table></body></html>'
);
exec msdb.dbo.sp_send_dbmail
@profile_name = @profile_name
,@recipients = @recipients
,@subject = @subject
,@body = @body
,@body_format = 'HTML';
end;
end;
go
May 28, 2014 at 9:34 am
Tables should be fully qualified, otherwise it'll generate some blood! ....from msdb..sysjobs inner join msdb..sysjobservers;
A quote needs to be closed after the word HTML'
August 5, 2014 at 11:15 pm
Good script 🙂
Either use
USE msdb
GO
in starting or use msdb.sys.....
Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply