Email Failed SQL Jobs

  • Comments posted to this topic are about the item Email Failed SQL Jobs

  • 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

  • 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'

  • 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