Sample Header Ad - 728x90

How do I create a SQL Agent Alert on a custom error message in SQL Server 2012?

6 votes
2 answers
5756 views
I want operators to be notified when I throw a custom error message. My understanding is that I need to add a message to sys.messages, then I can either RAISERROR or THROW that error ID. Creating an alert on the message ID and setting it to send to operators should create those notifications. I've created a SQL Agent alert on the message ID of 50005, and am using the following T-SQL to create the message: EXEC sp_addmessage @msgnum = 50005, @severity = 16, @msgtext = N'%s'; GO Then, if I execute this: RAISERROR (50005, -- Message id. 16, -- Severity, 1, -- State, N'My custom message'); THROW 50005, 'My custom message', 1; GO I get the following output as expected from both RAISERROR and THROW: > Msg 50005, Level 16, State 1, Line 68 My custom message When I view the history of the alert though, it shows that it has not been triggered, and the operator does not receive an email update. My agent alert looks like this: Agent Alert Setup Scripting the alert generates the following: USE [msdb] GO EXEC msdb.dbo.sp_update_alert @name=N'Alert DBA on custom errors', @message_id=50005, @severity=0, @enabled=1, @delay_between_responses=0, @include_event_description_in=1, @database_name=N'', @notification_message=N'', @event_description_keyword=N'', @performance_condition=N'', @wmi_namespace=N'', @wmi_query=N'', @job_id=N'00000000-0000-0000-0000-000000000000' GO EXEC msdb.dbo.sp_update_notification @alert_name=N'Alert DBA on custom errors', @operator_name=N'My Operator', @notification_method = 1 GO For your convenience, if you try to recreate my issue this will help you drop from sys.messages: sp_dropmessage @msgnum = 50005; GO
Asked by Dan Bowling (175 rep)
Jan 22, 2016, 07:05 PM
Last activity: Jan 22, 2016, 08:20 PM