How to notify an alert using an email operator.

Problem: How to notify an alert using an email operator.

Solution: sp_add_notification

Alerts are useful to notify when a job fails or an error occurs based on an error number or error message. This can be helpful for monitoring of the database disk errors as well.

As we have custom alerts that get generated when an event occurs that triggers this alert, so how do we notify this to a person or a group of people. This can be done by setting notifications for the alert using email operators.

To know more about email operators please go through this article. How to Create Email Operator

Example:

In this article I am going to show you we can setup notification for custom Alerts.

Example 1: In this example, I would like to setup notification to an operator when a particular alert is triggered. Assuming an operator already exists and alerts are created.

EXEC msdb.dbo.sp_add_notification

@alert_name=N'Maintenance Alert',

@operator_name=N'LearnersSQL',

 @notification_method = 1--(1- Email, 2-Pager, 3-NetSend)

GO

--To view notifications and operators for a specific alert you can run by this query.

select a.name as alert_name,o.name as operator_name,

CASE WHEN n.notification_method=1 THEN 'Email'

WHEN n.notification_method=2 THEN 'Pager'

WHEN n.notification_method=4 THEN 'NetSend' End as Notification_type

from msdb.dbo.sysnotifications n

INNER JOIN msdb.dbo.sysalerts a

ON a.id=n.alert_id

INNER JOIN msdb.dbo.sysoperators O

ON O.id=n.operator_id

alert_name	operator_name	Notification_type
Maintenance Alert	LearnersSQL	Email

Example 2: In this example let’s see how we can update the notification to send to a different operator.

EXEC msdb.dbo.sp_update_notification

@alert_name=N'Maintenance Alert',

@operator_name=N'TestOperator',

 @notification_method = 1

GO