| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | ||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sp_add_operator (Transact-SQL) |
Creates an operator (notification recipient) for use with alerts and jobs. |
markingmyname |
maghan |
randolphwest |
06/02/2023 |
sql |
system-objects |
reference |
|
|
|
[!INCLUDE SQL Server - ASDBMI]
Creates an operator (notification recipient) for use with alerts and jobs.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
sp_add_operator
[ @name = ] 'name'
[ , [ @enabled = ] enabled ]
[ , [ @email_address = ] N'email_address' ]
[ , [ @pager_address = ] N'pager_address' ]
[ , [ @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 = ] N'netsend_address' ]
[ , [ @category_name = ] 'category' ]
[ ; ]
The name of an operator (notification recipient). This name must be unique and can't contain the percent (%) character. @name is sysname, with no default.
Indicates the current status of the operator. @enabled is tinyint, with a default of 1 (enabled). If 0, the operator isn't enabled and doesn't receive notifications.
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.
You can specify either a physical e-mail address or an alias for @email_address. For example:
fatmir.bregu or [email protected]
Note
You must use the e-mail address for Database Mail.
The pager address of the operator. This string is passed directly to the e-mail system. @pager_address is nvarchar(100), with a default of NULL.
The time after which [!INCLUDE ssNoVersion] Agent sends pager notification to the specified operator on the weekdays, from Monday through Friday. @weekday_pager_start_time is int, with a default of 090000, which indicates 9:00 A.M. on a 24-hour clock, and must be entered using the form HHmmss.
The time after which [!INCLUDE ssnoversion-md] Agent service no longer sends pager notification to the specified operator on the weekdays, from Monday through Friday. weekday_pager_end_time is int, with a default of 180000, which indicates 6:00 P.M. on a 24-hour clock, and must be entered using the form HHmmss.
The time after which [!INCLUDE ssnoversion-md] Agent service sends pager notification to the specified operator on Saturdays. saturday_pager_start_time is int, with a default of 090000, which indicates 9:00 A.M. on a 24-hour clock, and must be entered using the form HHmmss.
The time after which [!INCLUDE ssnoversion-md] Agent service no longer sends pager notification to the specified operator on Saturdays. @saturday_pager_end_time is int, with a default of 180000, which indicates 6:00 P.M. on a 24-hour clock, and must be entered using the form HHmmss.
The time after which [!INCLUDE ssnoversion-md] Agent service sends pager notification to the specified operator on Sundays. @sunday_pager_start_time is int, with a default of 090000, which indicates 9:00 A.M. on a 24-hour clock, and must be entered using the form HHmmss.
The time after which [!INCLUDE ssnoversion-md] Agent service no longer sends pager notification to the specified operator on Sundays. @sunday_pager_end_time is int, with a default of 180000, which indicates 6:00 P.M. on a 24-hour clock, and must be entered using the form HHmmss.
A number that indicates the days that the operator is available for pages (subject to the specified start/end times). @pager_days is tinyint, with a default of 0 indicating the operator is never available to receive a page. Valid values are 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. The following table lists the value for each day of the week.
| Value | Description |
|---|---|
1 |
Sunday |
2 |
Monday |
4 |
Tuesday |
8 |
Wednesday |
16 |
Thursday |
32 |
Friday |
64 |
Saturday |
The network address of the operator to whom the network message is sent. @netsend_address is nvarchar(100), with a default of NULL.
The name of the category for this operator. @category_name is sysname, with a default of NULL.
0 (success) or 1 (failure).
None.
sp_add_operator must be run from the msdb database.
Your e-mail system must have an e-mail-to-pager capability if you want to use paging.
[!INCLUDE ssManStudioFull] provides an easy, graphical way to manage jobs, and is the recommended way to create and manage the job infrastructure.
[!INCLUDE msdb-execute-permissions]
The following example sets up the operator information for danwi. The operator is enabled. [!INCLUDE ssNoVersion] Agent sends notifications by pager from Monday through Friday from 8 A.M. to 5 P.M.
USE msdb;
GO
EXEC dbo.sp_add_operator @name = N'Dan Wilson',
@enabled = 1,
@email_address = N'danwi',
@pager_address = N'[email protected]',
@weekday_pager_start_time = 080000,
@weekday_pager_end_time = 170000,
@pager_days = 62;
GO