sp_addoperator SQL Executive Stored Procedure

Sets up an operator (notification recipient) for use with alerts.

Syntax

sp_addoperator name [, enabled] [, email_address] [, pager_number]
[, weekday_pager_start_time] [, weekday_pager_end_time] [, saturday_pager_start_time] [, saturday_pager_end_time] [, sunday_pager_start_time] [, sunday_pager_end_time] [, pager_days]

where

name

Is the name of the operator (notification recipient). This name must be unique and can be as many as 50 characters.

enabled

Indicates the operator's current status (1 if currently enabled, 0 if not). If an operator is not enabled, he or she will not receive notifications. The default is 1 (enabled).

email_address

Specifies the e-mail address of the operator. This string will be passed directly to the e-mail system. It can be as many as 100 characters.

pager_number

Specifies the pager address of the operator. This string will be passed directly to the e-mail system. It can be as many as 100 characters.

weekday_pager_start_time

Sets the start time after which to send pager notification to the specified operator Monday through Friday. It must be of the form HHMMSS. The default is 9:00 A.M.

weekday_pager_end_time

Sets the end time after which to cease pager notification to the specified operator Monday through Friday. It must be of the form HHMMSS. The default is 6:00 P.M.

saturday_pager_start_time

Sets the start time after which to send pager notification to the specified operator on Saturdays. It must be of the form HHMMSS. The default is 9:00 A.M. (if not supplied).

saturday_pager_end_time

Sets the end time after which to cease a pager notification to the specified operator on Saturdays. It must be of the form HHMMSS. The default is 6:00 P.M. (if not supplied).

sunday_pager_start_time

Sets the start time after which to send a pager notification to the specified operator on Sundays. It must be of the form HHMMSS. The default is 9:00 A.M. (if not supplied).

sunday_pager_end_time

Sets the end time after which to cease pager notification to the specified operator on Sundays. It must be of the form HHMMSS. The default is 6:00 P.M. (if not supplied).

pager_days

Specifies the days that the operator is available for pages (subject to the specified start/end times). Must be a value from 0 through 127. The value is calculated by adding together the individual values for the required days:

1Sunday
2Monday
4Tuesday
8Wednesday
16Thursday
32Friday
64Saturday

For example, Monday through Friday would be 2+4+8+16+32 = 62. If no value is supplied, the default is 0 (meaning that the operator is never available to receive a page).

Remarks

This stored procedure must be run from the msdb database.

Paging is supported via the e-mail system, which must have an e-mail-to-pager capability if you want to use paging.

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.

Example

This example sets up the operator information for John Doe.

EXEC sp_addoperator @name = 'John Doe', @email_address = 'JohnD@company.com'

Permission

Only the system administrator can use this procedure.

Tables Used

sysoperators