sp_help_notification
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 sysname, 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 sysname, 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 |
| use_pager | int | Pager is used to notify operator:
1 = Yes |
| use_netsend | int | Network pop-up is used to notify the operator:
1 = Yes |
| 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 |
| use_pager | int | Pager is used to send notification of the operator:
1 = Yes |
| use_netsend | int | Is a network pop-up used to notify the operator:
1 = Yes |
| has_email | int | Operator has an e-mail address:
1 = Yes |
| has_pager | int | Operator has a pager address:
1 = Yes |
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