CODEWITHSUNDEEP
sqlsql-serversp-send-dbmailazure-sql-managed-instancesql-azure-alerts
Van Darth Vadden
Van Darth Vadden

Reputation: 761

SQL Server job error on email notification

I have configured a database email, operators, and such on my SQL managed instance, to receive an email when a job fails.

In the email, we get something like this "The yyy_job failed on step 3".

But my question is... Is there a way to add the error message on the body of the email? I've been searching for this, but can't fine a suitable answer.

Thank you in advance

Upvotes: 1

Views: 3732

Answers (2)

Vladimir Baranov
Vladimir Baranov

Reputation: 32685

We have a similar set up. We have a SQL Server Agent job that consists of several steps.

I configured it in such a way that we receive notification email when the job starts and another email when it finishes. There are two versions of the final email - one for success, another for failure.

job steps

At the end of the job there are two final steps called "Email OK" and "Email FAIL". Note how each of the steps have their "On Success" and "On Failure" configured.

This is how "Email OK" and "Email FAIL" steps look like in our case:

email OK

email FAIL

In my case I simply have different subjects of the emails, so it is easy to filter in the email client.

You can write any extra T-SQL code to execute a query against msdb.dbo.sysjobhistory and include the relevant result into the email.

I will not write a complete query here, but I imagine it would look similar to my sketch below. If you need help with that, ask another question.

This is how you can use msdb.dbo.sp_send_dbmail to include the result of some query into the email text:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'ABC'
    ,@recipients = '[email protected]'
    ,@subject = 'Some subject line'
    ,@body = @VarBody
    ,@body_format = 'TEXT'
    ,@importance = 'NORMAL'
    ,@sensitivity = 'NORMAL'
    ,@query = N'
        -- show latest entry in the log for your job
        SELECT TOP(1)
            message, ...
        FROM
            msdb.dbo.sysjobhistory
        WHERE
            job_id = ''your job ID''
        ORDER BY
            instance_id DESC; 
        '
    ,@execute_query_database = 'msdb'
    ;

Have a look at the documentation for a list of parameters for sp_send_dbmail. Example above inlines the query result. You can also attach it as a separate file.

Upvotes: 0

Stewart
Stewart

Reputation: 835

As far as I know there's no way to add further details to the email notifications when a job fails.

The only way is to implement your own notification process.

https://www.sqlshack.com/reporting-and-alerting-on-job-failure-in-sql-server/

Upvotes: 1

Related Questions