Operators

Operators are used throughout SQL Server to specify an administrator who is alerted about an event such as an error or the completion of a job. You can also use operators to set on-call schedules.

Adding an Operator

To add an operator, first ensure that SQL Server Agent is started. Although SQLAgentMail doesn’t have to be configured, you can’t do too much with operators without it. (The only action you can perform without configuring SQLAgentMail is Net Send.)

To add an operator through Enterprise Manager, go to the Operators group under the Management | SQL Server Agent. Right-click the Operators group and select New Operator to open the New Operators Properties dialog box (see Figure 4-1).

Figure 4-1: Use Enterprise Manager to add and operator

On the General tab, name the operator and specify the contact information. Incidentally, even if you fill in all the contact information, SQL Server only uses the information when specifically requested. See the discussion on alerts in Chapter 5 for more details.

The name must be unique and cannot contain the percent (%) character. The E-mail Name option is the e-mail address, or the name in the profile’s address book for the operator.

If you use an e-mail address, wrap the address in brackets and add the SMTP syntax. This explicitly states that you’re sending e-mail, and therefore avoids the potential problem that MAPI or Agent could be confused about your intentions. For example, [e-mail] should be entered as [SMTP:[e-mail]].

The Pager E-mail Name option specifies the e-mail address for the alphanumeric pager of this operator. You can also use this option to specify an additional e-mail address to send alerts, based on an availability schedule. Specify a pager e-mail name with the SMTP syntax, then specify when the operator is on duty.

Use the Net Send Address option to send popup messages to a workstation when certain events occur. To use this feature, specify the name or IP address of the target computer.

Caution 

Most SQL Servers can’t use the Net Send feature because they’re behind firewalls that prevent this type of broadcast. In Chapter 5, I discuss some workarounds.

Tip 

Consider creating an e-mail alias for each job position where an operator is needed. If you have multiple servers, you can make any adjustment to where the e-mail is sent in one location. You can also send one message to multiple e-mail accounts this way or by using an e-mail distribution list.

Scripting Operators

So why would you ever need to script the creation of operators? Well, for most companies, the disaster recovery plan includes the possibility of restoring users on a new server. You could either restore the msdb database from a backup or run scripts. Scripting is also useful when you’re creating a setup program for your servers or creating a duplicate server.

You can add an operator using T-SQL with the sp_add_operator system stored procedure in the msdb database. Any time you add an operator, a row is added to the sysoperators table in the msdb database. Here is the full syntax for adding an operator:

sp_add_operator [ @name = ] '<operator's name>'     [ , [ @enabled = ] <bit field 1=yes, 0=no> ]     [ , [ @email_address = ] '<operator's e-mail address>' ]     [ , [ @pager_address = ] '<operator's pager e-mail address>' ]     [ , [ @weekday_pager_start_time = ] <weekday on duty start            time as 24 hour integer> ]     [ , [ @weekday_pager_end_time = ] <weekday on duty stop             time as 24 hour integer> ]     [ , [ @saturday_pager_start_time = ] <Saturday on duty start             time as 24 hour integer> ]     [ , [ @saturday_pager_end_time = ] <Saturday on duty stop            time as 24 hour integer> ]     [ , [ @sunday_pager_start_time = ] <Sunday on duty start            time as 24 hour integer> ]     [ , [ @sunday_pager_end_time = ] <Sunday on duty stop            time as 24 hour integer> ]     [ , [ @pager_days = ] <days operator can be paged> ]     [ , [ @netsend_address = ] '<operator's net send address>' ]     [ , [ @category_name = ] 'alert cateogory' ]

The @pager_days parameter is represented as a tinyint field that stores all the days in one field. To set the parameter, look at Table 4-2 and find the days you want to set for. Then add the days together to set the parameter.

Table 4-2: Days of the Week Represented Numerically

Day	Day's Value
Sunday		1
Monday		2
Tuesday		4
Wednesday		8
Thursday		16
Friday		32
Saturday		64

For example, you can use the following syntax to add the same operator that I added in the beginning of this section (refer to Figure 4-1):

DECLARE @PagerDays tinyint --Sets variable to hold Monday, Wednesday, Friday and Saturday SET @PagerDays = 2 + 8 + 32 + 64 EXEC msdb..sp_add_operator       @name = 'ShiftManager',       @enabled = 1,       @email_address = '[SMTP:[e-mail]]',       @pager_address = '[SMTP:[e-mail]]',       @netsend_address = 'workstationname',       @weekday_pager_start_time = 080000,       @weekday_pager_end_time = 180000,       @saturday_pager_start_time = 090000,       @saturday_pager_end_time = 180000,       @pager_days = @PagerDays

After you’ve added an operator through T-SQL, the operator appears in Enterprise Manager. You can also run sp_help_operator to see a list of all the operators and their settings.

To update an operator after it’s been added, you can use the sp_update_operator stored procedure, which takes all the parameters that sp_add_operator takes. You can delete an operator with the sp_delete_operator stored procedure, which expects the @name parameter.

If you don’t want to spend your time creating these scripts by hand, you can create them through Enterprise Manager, as follows:

• For an individual operator, right-click an individual operator and select All Tasks | Generate SQL Script.

• For all operators, right-click the Operators group in the left pane and select All Tasks | Generate SQL Script.