Operators

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.

Using Enterprise Manager to Create an Operator

To create an operator by using Enterprise Manager, follow these steps:

  1. In Enterprise Manager, expand a server folder, expand the Management folder, and then expand the SQL Server Agent folder. Right-click Operators and choose New Operator from the shortcut menu to display the New Operator Properties window, shown in Figure 31-22. On the General tab, type a name for the new operator, and then enter one or more of the following: that operator's e-mail address, pager address, and NET SEND address.

    click to view at full size.

    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.

  2. Click the Notifications tab. If you click Alerts (in the upper right corner of the tab), a list of any existing alerts is displayed, as shown in Figure 31-23. By selecting the check boxes in the appropriate columns, you can specify which alerts will cause the operator to be notified and by which method of communication the operator will be notified.

    click to view at full size.

    Figure 31-23. The Notifications tab of the New Operator Properties window, showing Alerts.

  3. When creating a new operator, you will not be able to click Jobs because no jobs could have been created that would notify the new operator, since that operator did not yet exist. To prevent this new operator from receiving notifications, clear the Operator Is Available To Receive Notifications check box. Disabling this option enables you to temporarily stop notifications from being sent to an operator—for example, while the operator is on vacation. You can then re-enable notifications by deselecting the check box when the operator returns.
  4. Click the Send E-mail button to create a test message to be sent to the operator listed on the General tab. (You will get an error if you did not enter an e-mail address on the General tab.) You can then send the e-mail, which describes the types of notifications that have been set up for that operator. At the bottom of the Notifications tab, you'll see information about the most recent notification attempts, by type, for that operator.

Using T-SQL to Create an Operator

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.



Microsoft SQL Server 2000 Administrator's Companion
Microsoft SQL Server 2000 Administrators Companion
ISBN: B001HC0RPI
EAN: N/A
Year: 2005
Pages: 264

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net