sp_update_operator
Updates information about an operator (notification recipient) for use with alerts and jobs.
Syntax
sp_update_operator [@name =] 'name'
[, [@new_name =] 'new_name']
[, [@enabled =] enabled]
[, [@email_address =] 'email_address']
[, [@pager_address =] 'pager_number']
[, [@weekday_pager_start_time =] weekday_pager_start_time]
[, [@weekday_pager_end_time =] weekday_pager_end_time]
[, [@saturday_pager_start_time =] saturday_pager_start_time]
[, [@saturday_pager_end_time =] saturday_pager_end_time]
[, [@sunday_pager_start_time =] sunday_pager_start_time]
[, [@sunday_pager_end_time =] sunday_pager_end_time]
[, [@pager_days =] pager_days]
[, [@netsend_address =] 'netsend_address']
[, [@category_name =] 'category']
Arguments
[@name =] 'name'
Is the name of the operator to modify. name is sysname, with no default.
[@new_name =] 'new_name'
Is the new name for the operator. This name must be unique. new_name is sysname, with a default of NULL.
[@enabled =] enabled
Is a number indicating the operator's current status (1 if currently enabled, 0 if not). enabled is tinyint, with a default of NULL. If not enabled, an operator will not receive alert notifications.
[@email_address =] 'email_address'
Is the e-mail address of the operator. This string is passed directly to the e-mail system. email_address is nvarchar(100), with a default of NULL.
[@pager_address =] 'pager_number'
Is the pager address of the operator. This string is passed directly to the e-mail system. pager_number is nvarchar(100), with a default of NULL.
[@weekday_pager_start_time =] weekday_pager_start_time
Specifies the time after which a pager notification can be sent to this operator, from Monday through Friday. weekday_pager_start_time is int, with a default of NULL, and must be entered in the form HHMMSS for use with a 24-hour clock.
[@weekday_pager_end_time =] weekday_pager_end_time
Specifies the time after which a pager notification cannot be sent to the specified operator, from Monday through Friday. weekday_pager_end_time is int, with a default of NULL, and must be entered in the form HHMMSS for use with a 24-hour clock.
[@saturday_pager_start_time =] saturday_pager_start_time
Specifies the time after which a pager notification can be sent to the specified operator on Saturdays. saturday_pager_start_time is int, with a default of NULL, and must be entered in the form HHMMSS for use with a 24-hour clock.
[@saturday_pager_end_time =] saturday_pager_end_time
Specifies the time after which a pager notification cannot be sent to the specified operator on Saturdays. saturday_pager_end_time is int, with a default of NULL, and must be entered in the form HHMMSS for use with a 24-hour clock.
[@sunday_pager_start_time =] sunday_pager_start_time
Specifies the time after which a pager notification can be sent to the specified operator on Sundays. sunday_pager_start_time is int, with a default of NULL, and must be entered in the form HHMMSS for use with a 24-hour clock.
[@sunday_pager_end_time =] sunday_pager_end_time
Specifies the time after which a pager notification cannot be sent to the specified operator on Sundays. sunday_pager_end_time is int, with a default of NULL, and must be entered in the form HHMMSS for use with a 24-hour clock.
[@pager_days =] pager_days
Specifies the days that the operator is available to receive pages (subject to the specified start/end times). pager_days is tinyint, with a default of NULL, and must be a value from 0 through 127. pager_days is calculated by adding the individual values for the required days. For example, from Monday through Friday is 2+4+8+16+32 = 62.
| Value | Description |
|---|---|
| 1 | Sunday |
| 2 | Monday |
| 4 | Tuesday |
| 8 | Wednesday |
| 16 | Thursday |
| 32 | Friday |
| 64 | Saturday |
[@netsend_address =] 'netsend_address'
Is the network address of the operator to whom the network message is sent. netsend_address is nvarchar(100), with a default of NULL.
[@category_name =] 'category'
Is the name of the category for this alert. category is sysname, with a default of NULL.
Return Code Values
0 (success) or 1 (failure)
Remarks
sp_update_operator must be run from the msdb database.
Permissions
Only members of the sysadmin fixed server role can execute sp_update_operator.
Examples
This example updates the operator status to enabled, and sets the days (from Monday through Friday, from 8 A.M. through 5 P.M.) when he can be paged.
USE msdb
EXEC sp_update_operator @name = 'Steven Buchanan', @enabled = 1,
@email_address = 'stevenb',
@pager_address = '[email protected]',
@weekday_pager_start_time = 080000,
@weekday_pager_end_time = 170000,
@pager_days = 62