sp_update_operator (T-SQL)

Updates information about an operator (notification recipient) for use with alerts and jobs.

Syntax

sp_updateoperator [@name =] 'name'
    [, [@new_name =] 'new_name']
    [, [@enable =] 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.

[@enable =] 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, 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, 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, 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)

Result Sets

None

Remarks

sp_updateoperator 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 (Monday through Friday, 8 A.M. through 5 P.M.) when he can be paged.

USE msdb

EXEC sp_updateoperator @name = 'Steven Buchanan', @enabled = 1,
    @email_address = 'stevenb',
    @pager_address = '5673218@mypagerco.com',
    @weekday_pager_start_time = 080000,
    @weekday_pager_end-time = 170000,
    @pager_days = 62

See Also