sp_help_notification (T-SQL)

Reports a list of alerts for a given operator or a list of operators for a given alert.

Syntax

sp_help_notification [@object_type =] 'object_type',
    [@name =] 'name',
    [@enum_type =] 'enum_type',
    [@notification_method =] notification_method
    [, [@target_name =] 'target_name']

Arguments

[@object_type =] 'object_type'

Is the type of information to be returned. object_type is char(9), with no default. object_type can be ALERTS, which lists the alerts assigned to the supplied operator name, or OPERATORS, which lists the operators responsible for the supplied alert name.

[@name =] 'name'

Is either an alert name (if object_type is ALERTS) or an operator name (if object_type is OPERATORS). name is char(100), with no default.

[@enum_type =] 'enum_type'

Is the object_type information that is returned. enum_type is ACTUAL in most cases. enum_type is char(10), with no default, and can be one of these values.

Value	Description
ACTUAL	Lists only the object_types associated with name.
ALL	Lists all the object_types including those that are not associated with name.
TARGET	Lists only the object_types matching the supplied target_name, regardless of association with name.

[@notification_method =] notification_method

Is a numeric value that determines the notification method columns to return. notification_method is tinyint, and can be one of the following values.

Value	Description
1	E-mail: returns only the use_email column.
2	Pager: returns only the use_pager column.
4	NetSend: returns only the use_netsend column.
7	All: returns all columns.

[@target_name =] 'target_name'

Is an alert name to search for (if object_type is ALERTS) or an operator name to search for (if object_type is OPERATORS). target_name is needed only if enum_type is TARGET. target_name is char(100), with a default of NULL.

Return Code Valves

0 (success) or 1 (failure)

Result Sets

If object_type is ALERTS, the result set lists all the alerts for a given operator.

Column name	Data type	Description
alert_id	int	Alert identifier number.
alert_name	sysname	Alert name.
use_email	int	E-mail is used to notify the operator: 1 = Yes 0 = No
use_pager	int	Pager is used to notify operator: 1 = Yes 0 = No
use_netsend	int	Network pop-up is used to notify the operator: 1 = Yes 0 = No
has_email	int	Number of e-mail notifications sent for this alert.
has_pager	int	Number of pager notifications sent for this alert.
has_netsend	int	Number of netsend notifications sent for this alert.

If object_type is OPERATORS, the result set lists all the operators for a given alert.

Column name	Data type	Description
operator_id	int	Operator identification number.
operator_name	sysname	Operator name.
use_email	int	E-mail is used to send notification of the operator: 1 = Yes 0 = No
use_pager	int	Pager is used to send notification of the operator: 1 = Yes 0 = No
use_netsend	int	Is a network pop-up used to notify the operator: 1 = Yes 0 = No
has_email	int	Operator has an e-mail address: 1 = Yes 0 = No
has_pager	int	Operator has a pager address: 1 = Yes 0 = No

Remarks

This stored procedure must be run from the msdb database.

Permissions

Permissions to execute this procedure default to the sysadmin fixed server role and the db_owner fixed database role, who can grant permissions to other users.

Examples

A. List alerts for a specific operator

This example returns all alerts for which the operator John Doe receives any kind of notification.

USE msdb

EXEC sp_help_notification 'ALERTS', 'John Doe', 'ACTUAL', 7

B. List operators for a specific alert

This example returns all operators who receive any kind of notification for the Test Alert alert.

USE msdb

EXEC sp_help_notification 'OPERATORS', 'Test Alert', 'ACTUAL', 7

sp_add_notification	sp_update_notification
sp_delete_notification	System Stored Procedures