3 4
Operators are individuals who can receive notification from SQL Server upon the completion of a job or the occurrence of an event. An operator is a person who is responsible for the maintenance of one or more systems running SQL Server. You've learned how to define the notification message that will be sent to the operator. As mentioned, the three methods used to communicate with operators are sending e-mail messages, paging, and using the NET SEND command (which sends a network message to the operator's computer). Your system must meet several prerequisites to make each of these methods work. For e-mail and pager communication, you must install a MAPI-1-compliant e-mail client ("MAPI" stands for "Messaging API") such as Microsoft Outlook or Microsoft Exchange Client on the server, and you must create a mail profile for SQLServerAgent. For paging, you will also need to install third-party e-mail-to-pager software on the mail server, which processes inbound e-mail messages and converts them to pager messages. To use NET SEND, you must be running Windows NT or Windows 2000 as your operating system—NET SEND is not supported under Microsoft Windows 95/98.
MORE INFO
For information about how to set up mail profiles, see your e-mail client software documentation. For information about pager-to-e-mail software, see your pager service provider or your pager documentation.
You must define each operator in SQL Server. You can create more than one operator in order to share responsibilities, as well as a fail-safe operator who will be notified when the others cannot be reached (for example, if a paging attempt fails). You can create an operator by using Enterprise Manager, T-SQL, or SQL-DMO. We will look at the Enterprise Manager and T-SQL methods in this section; the SQL-DMO method is beyond the scope of this book.
To create an operator by using Enterprise Manager, follow these steps:
Figure 31-22. The General tab of the New Operator Properties window.
If you enter a pager address, you can specify when that operator can be paged in the Pager On Duty Schedule area. For example, if you had more than one operator, you could divide their duties by allowing one operator to be paged on Monday, Wednesday, Friday, and Sunday and another operator to be paged on Tuesday, Thursday, and Saturday.
Figure 31-23. The Notifications tab of the New Operator Properties window, showing Alerts.
The T-SQL commands used to create an operator, modify operator information, view operator information, and delete an operator are these system stored procedures, found in the msdb database: sp_add_operator, sp_update_operator, sp_help_operator, and sp_delete_operator, respectively. Again, you might find using Enterprise Manager to be the easier method. You can generate the TSQL scripts after you have created the operators by using Enterprise Manager.
Here is the syntax for sp_add_operator:
sp_add_operator [@name =] 'name' [, [@enabled =] enabled] [, [@email_address =] 'email_address'] [, [@pager_address =] '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 =] 'netsend_address'] [, [@category_name =] 'category']
MORE INFO
For details about the options of the stored procedures listed in this section, look up the stored procedures by name in the Books Online index.