SQL Job Error Notification
I am a big proponent of not putting code in a database server that does anything other than serve up data. If data can be pivoted in a data consumer with less load on the server the offload the work. Formatting is a user presentation task, and is better done outside of the database server. It is not the job of your database to send Email…let it send data.
All that being said, I did much of the above when setting up a process to notify me of failed jobs in SQL Server. Not because it was the best or purest way to write code. I did it because it was the quickest way to solve my immediate need of notification when a scheduled SQL Server Agent job failed, and deploy on multiple instances of SQL Server.
Yesterday I demonstrated SQL to capture failed jobs from the MSDB database. Now all I need is a way to format that captured data into Email text, and send the results to a list of system operators. That capability is built into SQL Agent jobs on a job level, but only if the complete job fails, which does not meet my need. I need it to notify me if any step in the job fails while the job continues to complete other steps that may succeed.
I created a test job with two steps, each with SQL Commands that should fail. When capturing the results from the msdb.sys.sysjobhistory table I wanted to produce an Email with text looking like the following…
Sample Job Failure Email Message…
SQL Server Agent Job Failed Execution on One or More Steps
| Job Name | Create Fail History And Send Email |
| Server | VIRTDEVDB01DEV01 |
Failed Steps
Step Fail SQL Command
| Date/Time | 08/27/2013 15:23:56 |
| Database | master |
| Error | 208 – Invalid object name ”%.*ls”. |
| Error Severity | 16 – Indicates general errors that can be corrected by the user. |
| Job Step Message | Executed as user: CDWSQLAgentacct. Invalid object name ”FOO”. [SQLSTATE 42S02] (Error 208). The step failed. |
| Command | SELECT * FROM FOO — Should Fail Because there is no table FOO in the Master database |
Step Send Failed Email Notification
| Date/Time | 08/27/2013 15:23:56 |
| Database | SysAdmin |
| Error | 4121 – Cannot find either column "%.*ls" or the user-defined function or aggregate "%.*ls", or the name is ambiguous. |
| Error Severity | 16 – Indicates general errors that can be corrected by the user. |
| Job Step Message | Executed as user: CDWSQLAgentacct. Cannot find either column "BogusStoredProcedure" or the user-defined function or aggregate "SysAdmin.dbo.BogusStoredProcedure", or the name is ambiguous. [SQLSTATE 42000] (Error 4121). The step failed. |
| Command | EXEC BogusStoredProcedure |
I accomplished this by creating a stored procedure that is called as a scheduled job by the SQL Server Agent. By polling the job history in this fashion, I don’t have to put any error trapping or notification on any job…my scheduled task will find all failed job steps, and send me an Email with the results.
The stored procedure has the following steps…
- Capture the job steps that have failed since I last checked and saved the results into a temporary table
- Open a cursor on the temporary table and generate the text for the Emails. I format the text as HTML so it appears as above in my Outlook email. I just use basic HTML tags
- Send an Email using a stored procedure I wrote that uses the native SQL Server Database Mail SMTP engine. Be sure to set the email "Body Format" to "HTML" so that the message will render as demonstrated above. In fact, I simly copied a samle message generated in my database and pasted the HTML into this editorial.
Here is a look at the stored procedure I wrote for sending the Email. It simply uses the msdb.dbo.sp_send_dbmail stored procedure for sending the mail. The difference is that it looks up the distribution list from a table I use called Configuration. That way, I only have one place to maintain my operator email list.
The procedure also uses the Database Mail profile I defined called “Default”. It would use that profile anyway even if I did not specify it, because it is the default profile. This way the expectation is explicit.
USE sysAdmin
GO
PRINT 'PROCEDURE Admin_EXEC_SendOperationsEmail'
GO
IF OBJECT_ID('Admin_EXEC_SendOperationsEmail') IS NOT NULL
BEGIN
PRINT ' DROP PROCEDURE Admin_EXEC_SendOperationsEmail'
DROP PROCEDURE Admin_EXEC_SendOperationsEmail
END
GO
PRINT ' CREATE PROCEDURE Admin_EXEC_SendOperationsEmail'
GO
/*
TEST
EXEC Admin_EXEC_SendOperationsEmail
'Test Subject'
, 'Test Message'
, 'HTML'
*/
CREATE PROCEDURE Admin_EXEC_SendOperationsEmail
(
@Subject VARCHAR(512)
,@MSG VARCHAR(MAX) = ''
,@BodyFormat VARCHAR(10) = 'TEXT'
)
AS
SET NOCOUNT ON
-- Call scalar function that looks up the Email addresses of the = SysAdmin.dbo.admin_get_OperationsEmailGroup_sfn()
-- From SysAdmin.dbo.Configuration table
DECLARE @TO VARCHAR(512)
SET @TO
exec msdb.dbo.sp_send_dbmail
@Profile_Name = 'Default'
,@recipients = @TO
,@subject = @Subject
,@body = @MSG
,@body_format = @BodyFormat
GO
IF @@ERROR = 0
BEGIN
PRINT ' PROCEDURE Admin_EXEC_SendOperationsEmail CREATED SUCCESSFULLY :-)'
END
ELSE
BEGIN
PRINT ' CREATE PROCEDURE Admin_EXEC_SendOperationsEmail FAILED :-('
END
GO
All of the code the last three days has been based on SQL Server 2008. It works with different versions of SQL Server but may require some tweaking to perfect for your specific installation.
That pretty much finishes up my tip for the week. Hope it has been useful. Leave a comment or drop me an Email at btaylor@sswug.org if you have any questions, corrections or improvements.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
Encryption Key Management Simplified
Managing encryption keys is a fundamental step to securing encrypted data, meeting compliance requirements, and preventing da… (read more)