sp_update_operator

Transact-SQL Reference

Transact-SQL Reference

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

See Also

sp_add_operator

sp_delete_operator

sp_help_operator

System Stored Procedures