sp_add_notification (T-SQL)

Sets up a notification for an alert.

Syntax

sp_add_notification [@alert_name =] 'alert',
    [@operator_name =] 'operator',
    [@notification_method =] notification_method

Arguments

[@alert_name =] 'alert'

Is the alert for this notification. alert is sysname, with no default.

[@operator_name =] 'operator'

Is the operator to be notified when the alert occurs. operator is sysname, with no default.

[@notification_method =] notification_method

Is the method by which the operator is notified. notification_method is tinyint, with no default. notification_method can be one or more of these values, ORed together.

 

Value	Description
1	E-mail
2	Pager
4	net send

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

sp_add_notification must be run from the msdb database.

SQL Server Enterprise Manager provides an easy, graphical way to manage the entire alerting system. Using SQL Server Enterprise Manager is the recommended way to configure your alert infrastructure.

To send a notification in response to an alert, you must first configure Microsoft® SQL Server™ Agent to send mail. For more information, see Configuring the SQLServerAgent Service.

If a failure occurs when sending an e-mail message or pager notification, the failure is reported in the SQL Server Agent error log.

Permissions

Only members of the sysadmin fixed server role can execute sp_add_notification.

Examples

This example adds an e-mail notification for the specified alert (Test Alert).

Note This example assumes that Test Alert already exists and that stevenb is a valid operator name.

USE msdb

GO

EXEC sp_add_notification 'Test Alert', 'stevenb', 1

See Also