Create custom notifications with SQL Server 2005

DBA Tim Chapman builds a small custom notification system with SQL Server that allows you to send a message to the correct people when a problem occurs.

In my article on sending e-mail with Database Mail in SQL Server 2005, I discussed how crucial it is to be notified when problems occur so that you can make adjustments to fix the problem and ultimately prevent it from happening again. This week, I will build a small custom notification system that allows you to send a message to the correct people when a problem occurs.

Note: This article is built upon Service Broker and Database Mail. If you do not have these two components set up on your SQL Server 2005 instance, the examples will not function properly. Please refer to my previous articles to aid you in configuring these two features.

Setting up the application

The application will take advantage of Service Broker's asynchronous messaging capabilties and internal activation to queue a notification message and send it in the background. Listing A creates the Service Broker objects that I will use.

Get SQL tips in your inbox
TechRepublic's SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.
Automatically sign up today!

I also need Database Mail running on the server. I will use the same profile and account that I created in my previous article for this example.

I'll need a table to store who needs to receive the notifcations. The script for this table is below.

CREATE TABLE NotificationSeverity
(
      IDCol SMALLINT IDENTITY(1,1) PRIMARY KEY,
      Severity SMALLINT,
      SeverityDescription VARCHAR(50)
      Email VARCHAR(75)
)
GO

Next, add some records to this table to make sure someone receives the message. View Listing B.

Listing C creates a stored procedure on the server that will be used to take messages off of our NotificationQueue and handle them. This procedure is our "Internal Activation" procedure, meaning that this procedure will be invoked when a message is received on the NotificationQueue.

There are a couple of things to notice in this procedure if you are not familiar with some of the new features to SQL Server 2005. The TRY…CATCH constructs provide you with an easier facility to catch errors. In fact, you can use an all-purpose procedure to catch these errors and log them into a table for later research. For more information on how TRY…CATCH works in SQL Server 2005, refer to this article.

Take note of the statement in the script above that begins with SELECT @eMailList =. This statement uses FLWOR, which is an XML query construct. I am essentially taking the e-mail addresses returned from the query and concatenating them together and separating them with a semicolon. You can use this @eMailList variable as a parameter in the call to the database mail procedure.

The next step is to alter the NotificationQueue so that it knows to automatically activate the procedure created above when a message reaches the Queue.

ALTER QUEUE NotificationQueue WITH ACTIVATION 
(
      STATUS = ON,
      MAX_QUEUE_READERS = 1,
      PROCEDURE_NAME = usp_RecordNotificationMessage,
      EXECUTE AS OWNER
);

GO

Finally, you need to create a procedure that sends the notifications. This procedure will only need to know what problem has occurred and the severity of the problem. The other components will take the message that this procedure sends and determine who needs to be notified based upon our NotificationSeverity table. See Listing D.

If you call the procedure, a message will be placed on the NotificationQueue, and the internal activation procedure will be called to handle the message.

EXECUTE usp_SendNotification 'A Severe Error has occurred.', 1

Enhancing the application

This example will do a pretty good job of keeping the necessary users notified of when problems occur in your system. However, you can enhance it to be more beneficial to you. One example would be to put the call to send the notification inside the usp_LogProcedureError procedure and let that procedure determine the severity of the problem based upon the type of error that has occurred.

Small enhancements like that, and thinking somewhat differently in terms of how notifications can be processed, can potentially lead to allowing you to find problems and preferrably create a system to find these errors before they even occur.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

By Tim Chapman

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.