sp_helpnotification SQL Executive Stored Procedure

Reports a list of notifications.

Syntax

sp_helpnotification object_type, name, enum_type, notification_method [, target_name]

where

object_type

Indicates the type of information to be returned. Can be ALERTS for a list of alerts assigned to the supplied operator name, or OPERATORS for a list of operators responsible for the supplied alert name.

name

Is either an operator name or an alert name.

enum_type

Controls what object_type information is returned. Specify ACTUAL (to see only the object_types associated with name), ALL (to see all the object_types including those that are not associated with name), or TARGET (to see only the object_type matching the supplied target_name, irrespective of association with name). This parameter will be ACTUAL in most cases.

notification_method

Specifies the notification method: EMAIL, PAGER or BOTH. When enum_type is ALL or TARGET, affects only the column headings appearing in the output. When enum_type is ACTUAL, affects both the column headings and the returned rows.

target_name

Specifies an alert name to search for (if object_type is ALERTS) or an operator name to search for (if object_type is OPERATORS). Needed only if enum_type is TARGET.

Remarks

This stored procedure must be run from the msdb database.

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

Examples

A.    Alerts for a Specific Operator

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

sp_helpnotification 'ALERTS', 'John Doe', 'ACTUAL', 'BOTH'

B.    Operators for a Specific Alert

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

sp_helpnotification 'OPERATORS', 'Test Alert', 'ACTUAL', 'BOTH'

Permission

Only the system administrator can use this procedure.

Tables Used

sysalerts, sysnotifications, sysoperators

See Also