sp_updateoperator SQL Executive Stored Procedure

Updates information about an operator.

Syntax

sp_updateoperator name [, new_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 original name of the operator.

new_name

Is the new name for the operator. 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 starting time after which to send a pager notification to this 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 a 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 a pager notification to the specified operator on Saturdays. It must be of the form HHMMSS. The default is 9:00 A.M.

saturday_pager_end_time

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

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.

sunday_pager_end_time

Specifies 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.

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, regardless of the pager schedule for that operator).

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.

Example

This example updates the operator status to enabled.

sp_updateoperator @name = 'John Doe', @enabled = 1

Permission

Only the system administrator can use this procedure.

Tables Used

sysoperators

See Also