Sending SQL Server Job Notifications to Multiple Operators

When our organization first implemented SQL Server 7.0, we had a client who required us to provide a range of DBA, development, and production support services. A team of onsite database professionals managed the services and rotated production support duties among themselves. Thus, we needed to inform the entire DBA team of critical job failures. However, in SQL Server 7.0, you can notify only one operator of a job's success or failure. Here's how we worked around this restriction.

First, we needed to define specific development rules about how the DBA and SQL Server developer teams would use SQL Server user-defined error messages. Our SQL Server developers were already using user-defined error messages to raise application-specific errors from within stored procedures and triggers. We agreed that developers would use a range of error message IDs between 50,000 and 79,999 for application errors. We decided to allocate the error message range 80,000 to 89,999 for production support purposes. Also, we wanted to assign a severity level for production support errors, and we needed to choose a severity level not assigned to any existing error messages. We decided to use severity level 9. Listing 1 shows the code we used to set up these defaults.

Next, in Enterprise Manager, we navigated to Management, SQL Server Agent and created a new alert. On the General tab of the DBA Alerts Properties dialog box, we defined a SQL Server alert to trap all severity level 9 errors, as Figure 1 shows. We could then invoke this alert to trap new custom SQL Server errors assigned to this severity level. To use this technique in T-SQL, you can invoke the error message by using RAISERROR commands that have severity level 9.

SQL Server alerts are generated by messages that the Windows NT application event log stores. SQL Server automatically logs messages of severity level 19 or higher. To get the NT event log to record our messages of severity level 9, we needed to use the RAISERROR WITH LOG clause, as Figure 2 shows. Using the RAISERROR WITH LOG clause let our offsite support teams use modem and dial-up services from remote sites to scan through the NT event log to find the messages they needed.

On the Response tab of the DBA Alerts Properties dialog box, we listed all the DBA team members who were set up as operators. Because we didn't have SQL Mail set up, we chose to use the NET SEND NT command to send alerts to specific workstations.

We had several time-critical batch tasks set up for our client, and the DBA team had to work proactively to detect impending problems and resolve them as quickly as possible. For example, one of the tasks we set up required detecting the arrival of feed files from our client's Asia office. The files arrived near the end of the business day in the Asia office—mid-morning in London. The DBA team needed to know whether the files failed to arrive by 11:30 a.m. so that we could contact the Asia team before they finished their shift. SQL Server alerts seemed the best way to get the notification to everyone who needed it.

We defined the feed-file detection job above on the Steps tab of the Check for HK file Properties dialog box. Initially, the task had one step with the On success event set to Quit with success and the On failure event set to Quit with failure. We needed to redirect all failed steps in the job to one exit step. This step could then execute the RAISERROR WITH LOG command so that the DBA team could read the Event Detail for messages of severity level 9 and get the information they needed. You can also apply this technique to SQL Server 2000 installations.

—Terry Catt
[email protected]