Defining Operators

The SQL Server Agent service supports the notification of administrators through operators. Operators are aliases for people or groups that can receive electronic notification when jobs have completed or alerts have been raised. The primary attributes of an operator are:

  • Operator name

  • Contact information

It is recommended that you define operators before you define alerts.

Naming an Operator

Every operator must have a name. Operator names must be unique within the SQL Server instance and can be no longer than 128 characters.

Providing Contact Information

An operator's contact information defines how the operator is notified. Operators can be notified by e-mail, pager, or through the net send command:

Important

The Pager and net send options will be removed from SQL Server Agent in a future version of Microsoft SQL Server. Avoid using these features in new development work, and plan to modify applications that currently use these features.

  • E-mail notification

    E-mail notification sends an e-mail message to the operator. For e-mail notification, you provide the e-mail address for the operator.

  • Pager notification

    Paging is implemented by e-mail. For pager notification, you provide the e-mail address where the operator receives pager messages. To set up pager notification, you must install software on the mail server that processes inbound mail and converts it to a pager message. The software can take one of several approaches, including:

    • Forwarding the mail to a remote mail server at the site of the pager provider.

      The pager provider must offer this service, although the software required is generally available as part of the local mail system. For more information, see your pager documentation.

    • Routing the e-mail by way of the Internet to an e-mail server at the pager provider's site.

      This is a variation on the first approach.

    • Processing the inbound e-mail and dialing the pager by using an attached modem.

      This software is proprietary to pager service providers. The software acts as a e-mail client that periodically processes its inbox either by interpreting all or part of the e-mail address information as a pager number, or by matching the e-mail name to a pager number in a translation table.

      If all of the operators share a pager provider, you can use SQL Server Management Studio to specify any special e-mail formatting that is required by the pager-to-e-mail system. The special formatting can be a prefix or a suffix and can be included in the following lines of the e-mail:

      Subject:

      Cc:

      To:

    Note

    If you use a low-capacity alphanumeric paging system, you can shorten the text that is sent by excluding the error text from the pager notification. An example of a low-capacity alphanumeric paging system is one that is limited to 64 characters per page.

  • net sendnotification

    This sends a message to the operator by means of the net send command. For net send, specify the recipient (computer or user) of a network message.

    Note

    The net send command uses Microsoft Windows Messenger. To successfully send alerts, this service must be running on both the computer on which SQL Server is running and the computer that the operator uses.

Requirements for Notifying an Operator

You must set up one or more of the following in order to notify an operator:

  • To send e-mail with Database Mail functionality, you must have access to an e-mail server that supports SMTP.

  • To send e-mail with SQL Mail (Extended MAPI) functionality, you must have access to a Microsoft Exchange server and you must install Microsoft Outlook and Microsoft Exchange client on the computer on which SQL Server is running.

  • For paging, you must have third-party pager-to-e-mail software and/or hardware.

  • To use net send, the operator must be logged on to the specified computer, and the specified computer must allow messages from Windows Messenger.

Designating a Fail-Safe Operator

The fail-safe operator receives an alert notification after all pager notifications to the designated operators have failed. For example, if you have defined three operators for pager notifications and none of the designated operators can be paged, the fail-safe operator is notified.

The fail-safe operator is notified when:

  • The operators responsible for the alert could not be paged.

    Reasons for failure to reach primary operators include incorrect pager addresses and off-duty operators.

  • SQL Server Agent cannot access system tables in the msdb database.

    The sysnotifications system table specifies operator responsibilities for alerts.

The fail-safe operator is a security feature. You cannot delete the operator assigned to fail-safe duty without reassigning fail-safe duty to another operator, or deleting the fail-safe assignment altogether.

Following are links to common tasks involved in defining operators: