Send db mail through SQL Agent job

  • Hi All,

    I have created SQL JOB and configured Db mail also for respective job. My requirement is that , if the job is failed or success I should get notification mail with reason, why job has failed. There is so many job has created so I can't go and check each job history. I have setup job and I am getting notification like below:

    JOB RUN:'Mail on login failed : State 5' was run on 3/16/2015 at 2:41:03 PM

    DURATION:0 hours, 0 minutes, 0 seconds

    STATUS: Failed

    MESSAGES:The job failed. The Job was invoked by User . The last step to run was step 1 (Step 1 - send e-mail).

    But As per the above notification I could not find the reason behind the failure so I can go check job history and find the reason. Instead of checking job history I want to display error message here itsself.

    Can anyone please help me to resolve this issue.

    Thanks in advance.

  • Your configuration means you are getting an alert sent to the operator configured for the job.

    From your description what you really want is an email that summarises the failure.

    I have a SSIS package that does something similar which loops over all our servers, and sends a summary email of all job failures that have not subsequently been successful looking back over X hours.

    I can't share the package as it has some sensitive data in it but I have included some of the code below to help you along:

    Create this procedure on your sever you want to report from in a suitable database:

    CREATE PROCEDURE usp_report_failed_jobs @CheckOverLastXHours int AS

    BEGIN

    -- Build list of all succeeded jobs

    WITH CTE_MostRecentSuccess AS (

    select j.name AS JobName,

    MAX(msdb.dbo.agent_datetime(run_date,run_time) )AS [Scheduled Run Date]

    FROM msdb..sysjobs j

    JOIN msdb..sysjobhistory h on j.job_id = h.job_id

    WHERE j.enabled = 1

    AND h.run_status = 1

    AND h.step_name = '(Job outcome)'

    AND h.run_time >= 1000

    AND msdb.dbo.agent_datetime(run_date,run_time) > DATEADD(hh,-@CheckOverLastXHours,getdate())

    GROUP BY j.name),

    ---- Build list of all failed job steps

    CTE_Currentfailures AS (

    select j.name AS JobName,

    MIN(CASE WHEN step_name = '(Job outcome)' THEN [message] END) AS [Step Failure Message],

    MAX(msdb.dbo.agent_datetime(run_date,run_time) )AS [Scheduled Run Date],

    ROW_NUMBER() OVER (PARTITION BY j.name ORDER BY run_date DESC, run_time DESC) RankNo

    FROM msdb..sysjobs j

    JOIN msdb..sysjobhistory h on j.job_id = h.job_id

    WHERE j.enabled = 1

    AND h.run_status <> 1

    AND h.step_name = '(Job outcome)'

    AND msdb.dbo.agent_datetime(run_date,run_time) > DATEADD(hh,-@CheckOverLastXHours,getdate())

    GROUP BY j.name, h.run_date, h.run_time)

    -- Report a List of jobs which latest status is a failure in the last 8 hours.

    SELECTCAST(@@ServerName AS NVARCHAR(100)) AS Server,

    CAST(f.JobName AS NVARCHAR(1000)) AS [Job Name],

    CAST(f.[Step Failure Message] AS NVARCHAR(4000)) AS [Step Failure Message] ,

    f.[Scheduled Run Date]

    FROM CTE_Currentfailures f

    LEFT JOIN CTE_MostRecentSuccess s on f.JobName = s.JobName

    WHERE f.RankNo = 1

    AND f.[Scheduled Run Date] > ISNULL(s.[Scheduled Run Date],'1980-01-01')

    AND f.[Scheduled Run Date] > DATEADD(hh,-@CheckOverLastXHours,getdate())

    END

    Then call the procedure and send the results via an email with HTML format this code gives you a basis for the email sending:

    SET NOCOUNT ON

    --Declare variables for use througout the code.

    DECLARE @EmailRecipient VARCHAR(1000)

    DECLARE @emailSub NVARCHAR(256)

    DECLARE @ProfileName VARCHAR(1000)

    DECLARE @jobHTML VARCHAR(MAX)

    DECLARE @status INT

    DECLARE @LastXHours INT

    DECLARE @Environment NVARCHAR(100)

    DECLARE @ServerAdminGroup NVARCHAR(100)

    -- SET Variable Values

    SET @EmailRecipient = 'test@yourcompanycom'

    SET @LastXHours = 1

    SET @Environment = 'PRODUCTION'

    /**********************************************************************/

    /*!!!!!!!!!!!!!!!! DO NOT MODIFY BELOW THIS LINE !!!!!!!!!!!!!!!!!!!!!*/

    /**********************************************************************/

    SELECT TOP 1 @ProfileName = name FROM msdb.dbo.sysmail_profile

    -- Remove Failed Jobs from the list if they have an exception

    DELETE FROM tmp_sql_failedjobs

    WHERE EXISTS (

    SELECT *

    FROM sql_failedjobs_exceptions E

    WHERE E.Server = tmp_sql_failedjobs.Server

    AND E.JobName = tmp_sql_failedjobs.JobName

    )

    -- if all previous top X excecutions of the job are over the threshold

    -- start formatting the email.

    SET @status =CASE WHEN (SELECT COUNT(1) FROM tmp_sql_failedjobs) > 0 THEN 1

    ELSE 2

    END

    SET @emailSub = CASEWHEN @status = 1 AND @Environment = 'PRODUCTION' THEN '!!! PRODUCTION Job Failures Detected !!!'

    WHEN @status = 1 AND @Environment <> 'PRODUCTION' THEN '!!! NON PRODUCTION Job Failures Detected !!!'

    END

    --Set the html text for the jobs and or package failures.

    SET @JobHTML =

    N'<H3 style="color:red; font-family:verdana">Job failures in the last '

    + CONVERT(VARCHAR(10),@LastXHours) + ' hours have been detected which have no subsequent successful completion, see details below' +

    '</H3>' +

    N'<p align="left" style="font-family:verdana; font-size:8pt">

    ' +

    N'<table border="3" style="font-size:8pt; font-family:verdana; text-align:left">' +

    N'<tr style="color:42426F; font-weight:bold"><th>Admin Group</th><th>Environment</th><th>Server</th><th>Job Name</th><th>Step failure Message</th><th>Execution Date</th></tr>' +

    CAST((

    SELECT

    td = ServerAdminGroup, '',

    td = EnvType, '',

    td = Server, '',

    td = JobName, '',

    td = StepFailureMessage, '',

    td = ScheduledRunDate

    FROM tmp_sql_failedjobs

    ORDER BY Server, JobName

    FOR XML PATH('tr'), TYPE

    ) AS VARCHAR(MAX) ) +

    N'</table>'

    IF EXISTS (SELECT TOP 1 1 FROM tmp_sql_logprogress WHERE JobCollectionSucceeded = 0 OR ConnectionAvailable = 0)

    BEGIN

    SELECT @jobHTML = @jobHTML + N'<p style="color:blue; font-family:verdana">The following servers encountered an error when performing failed job collection ''' +

    N'<p align="left" style="font-family:verdana; font-size:8pt">

    ' +

    N'<table border="3" style="font-size:8pt; font-family:verdana; text-align:left">' +

    N'<tr style="color:42426F; font-weight:bold"><th>Type</th><th>Category</th><th>Server</th><th>Connect Success</th><th>Collect Success</th><th>ErrorDetails</th></tr>' +

    CAST((

    SELECT

    td = ServerAdminGroup, '',

    td = EnvType, '',

    td = Server, '',

    td = CASE WHEN ConnectionAvailable = 1 THEN 'Y' ELSE 'N' END, '',

    td = CASE WHEN JobCollectionSucceeded = 1 THEN 'Y' ELSE 'N' END, '',

    td = ISNULL(ErrorDetails,'N/A'),''

    FROM tmp_sql_logprogress

    WHERE ConnectionAvailable = 0 OR JobCollectionSucceeded = 0

    ORDER BY ServerAdminGroup, EnvType, Server

    FOR XML PATH('tr'), TYPE

    ) AS VARCHAR(MAX) ) +

    N'</table>'

    END

    IF @status = 1

    BEGIN

    --Send the status report email

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = @ProfileName,

    @recipients=@EmailRecipient,

    @body=@jobHTML,

    @body_format = 'HTML',

    @subject =@emailSub

    END

    SELECT @JobHTML

    This is not a complete solution, but gives you a start.

    MCITP SQL 2005, MCSA SQL 2012

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply