tarting with SQL Server 2000, Microsoft added the concept of notification services to their flagship database engine. SQL Server Notification Services lets you generate and send notifications whenever some criterion is met. Generally, you use notifications to alert or update data for users or applications without having to repeatedly poll the database to discover whether data has changed or to display the latest data. For example, if you follow fantasy sports, it would be nice to have a notification service that informs you when your star player is injured. Alternatively, if you prefer the stock market, you may want to be notified when a stock you own reaches a particular price on the upward or downward curve!

With SQL Server 2005, you can generate notifications and have the SQL Server distributor send those notifications in any of several ways:

• On a file channel, where a file is written to a specific directory with the contents of the notification.
• On an SMTP channel where SQL Server e-mails the notification.
• On an HTTP channel, which can be customized to deliver the notification in a number of ways, such as a SOAP message or HTTP POST of the notification to a server.

In this article you’ll see how to set up Notification Services within SQL Server 2005, learn how to deploy and use the Stock Quote notification service and finally, how to take advantage of notifications with Java.

Getting Started
When installing SQL Server 2005, make sure that you install the samples and tutorials as you’ll need them to create the application. The setup isn’t the friendliest for post-installation configuration, so you can save yourself a lot of hassle by finding the SQLServerSamples.msi file in the C:Program FilesMicrosoft SQL Server90ToolsSamples directory.

First, you’ll need to compile the sample code for the stock quotes that you’ve just installed. Using Visual Studio, browse to the C:Program FilesMicrosoft SQL Server 2005 SamplesNotification ServicesStock directory and open the Stock.sln (for C#) or Stock_VB.sln (for VB.NET) solution. After the solution loads, perform build the application by selecting “Build Solution” from the Build menu. This will compile all the console applications that you’ll be using a little later. The details of what these applications do is beyond the scope of this article?for this project, you’re only interested in getting SQL Server notifications from them?but the online documentation is a great resource if you want more information about the applications themselves.

After building the samples, you’ll need to set up the notification service itself and configure it to use the applications that you’ve just built.

To simplify configuration, Microsoft lets you store instance configuration in an XML file, so all you need to do is to point Notification Services at this configuration file to create a configured instance. For the Stock Quote sample, you’ll see a file called InstanceConfig.xml in the same directory as the solution file that you opened earlier. Here’s a snippet:

 StockInstance  %SqlServer%                  Stock                    %SampleDirectory%AppDefinition                appADF.xml                                                 _DBSystem_            %SqlServer%                    ...                   

The important setting here is , which is where you specify (another) XML file that outlines the actual application definition, called an ADF file. So, you have to build two primary entities for a notification service: the notification instance itself, and the application that implements it.

You can see an excerpt of the ADF file from the sample project below. The portion shown specifies the parameters to an event.

    StockEvents            StockSymbol      nvarchar(6)      not null        ...    

At first, all this might seem a little overwhelming, but as you use Notification Services, you’ll find that the process quickly becomes more intuitive. The best thing to do is to start from a good example (SQL Server 2005 provides several) and build from there.

Creating a Notification Service Instance
Next, you need to create a notification service instance that uses the configuration files described in the previous section.

To do that, start the SQL Server Workbench. Select an instance of SQL Server, and then select “Notification Services” from the object browser. Click “New Notification Services Instance?” and you’ll see the dialog shown in Figure 1.

Fill out the dialog as shown in Figure 1?but start by setting the configuration file. Click “Browse?” to find the InstanceConfig.xml file and then update the dialog settings to match your system. Be sure to check the “Enable Instance” checkbox at the bottom.

Figure 1. Creating a New Notification Services Instance: To set up the sample notification, fill out the dialog as shown.

Register the Instance
Now you need to register your new notification service instance with SQL Server. To do this, find the notification service instance that you just created in the object browser, right click it and select “Register” from the “Tasks” menu.

Before going to the next step, you should create a new Windows user that Notification Services will use to access the database. In this example I created a standard user called NServices.

Using the security node on the object browser, you can then select the “Logins” node and right click it. Select the “New Login” option. From this dialog, select the account that you just created and click “OK.”

Now you’ll see that the account (i.e. NServices) has become a login to SQL Server, listed under the Logins node. For the StockInstanceNSMain and StockInstanceStock databases you should have the NSRunService role checked (see Figure 2 for details).

This example notification service writes notifications to a file in a specific directory (the Notifications directory in the sample application folder in this case). It also reads events from a file in a specific directory (the Events directory in the same folder). Consequently, you need to ensure that the process has sufficient access rights to the file system folders used to drop notification events and delivered notifications.

Figure 2. Configuring Security: On the Login Properties dialog, configure the databases as shown, adding the NSRunService role to both.

To do that, browse to the directory C:Program FilesMicrosoft SQL Server 2005 SamplesNotification ServicesStock using Windows Explorer, right click the directory name, and then click the Security tab. Give the windows account you created earlier (i.e. NServices) file read and write access.

Running the Notification Service
Finally, you need to run the Notification Services applications. There are three of those, and you’ll find them all in subdirectories of the C:Program FilesMicrosoft SQL Server 2005 SamplesNotification ServicesStock directory. Depending on which language you chose for the sample (VB.NET or C#) the locations of these three notification services differ, but as you browse through the directories you’ll find the applications easily. All three are in directories with the same name as the app in question.

First, run HostableExecutionEngine.exe. This application runs in a console and asks you to press the Enter key to exit. Don’t exit it yet! You’ll use this application to launch your notification service instance.

Second, run the AddSubscribers.exe application, which adds subscribers to the services.

Third, and lastly, run the AddSubscriptions.exe application. This, as you can imagine, sets up the subscription parameters for your subscribers.

Now that your notification services are up and running, you can test them by finding the EventData.xml file in the root folder of the sample application, and copying it to the Events folder. Notification Services will detect the file drop, and do its thing. After a short time, it will create a new file in the Notifications folder containing the details of the notification.

Applications can then monitor the Notifications folder for updates to this file, and handle them appropriately. In this case, the file contains notifications of when stock prices reach their desired thresholds. An application monitoring this directory could then (for example) send an instant message to subscribers informing them of this fact.

Getting SMTP and HTTP Notifications
Notification Services aren’t limited to delivering notifications via files. You can also configure notification service instances to deliver notifications on an SMTP channel, so if you have an e-mail server set up that they can access, they can e-mail subscribers for you. In addition, Notification Services support an HTTP channel that your application can extend to allow (among others), SOAP messages to be posted to a Web service, instant messages to be posted through an HTTP gateway, or any other permutations that you can think of.

Getting Java Into the Act
For the sake of simplicity and brevity, this article focuses on the file-drop method that the Stock sample uses. The best design for an application such as this is to have Notification Services write to a shared network directory that the Java application has access to. For this sample though, both Notification Services and the Java application run on the same machine, and the notifications directory has been mapped to the “F” drive. For example, here’s some very simple Java code that monitors the drop directory for updates.

   package com.devx.ns4j.mypackage;      import java.util.*;    import java.io.*;   public class nsfilemonitor extends TimerTask   {       private File theFile;      private long theFileTime;      private String strFile;      public nsfilemonitor(String sFile)      {         strFile = sFile;      }         public void setFile()      {         theFile = new File(strFile);         theFileTime = theFile.lastModified();      }            public static void main(String[] args)      {         nsfilemonitor nMon = new nsfilemonitor(            "F:\filenotifications.txt");         nMon.setFile();         Timer thetimer = new Timer (true);          thetimer.schedule(nMon,0,1000);         while(true)         {                  }      }               public void run()       {          long nCurrentFileTime = theFile.lastModified();         if (nCurrentFileTime!=theFileTime)         {            // Alert -- we have a change in the             // notification file            // We should then also reset the File time             // using setFile            String strDoc = "";            String strLine = "";            try            {               BufferedReader bIn =                   new BufferedReader(new                   FileReader(theFile));               while ((strLine = bIn.readLine()) != null)                    strDoc+=strLine;            }            catch (Exception ex)            {               ex.printStackTrace();            }            setFile();         }         else         {            // Do nothing            String strTest = "Hello";         }      }   } 

This application shown above is very straightforward. It caches the modification date and time of the file to watch, and sets up a timer that ticks every 1000 milliseconds. After each timer tick it compares the current modified date of the file with the cached one. If they differ, the file has changed within the last second, so the application opens it and reads the contents into the strDoc string. The application then resets the cached modified date to the current one.

This sample application doesn’t do anything with the notification beyond reading it into memory; but in a real application you would parse what you need out of it (the notification file is formatted as XML) and use the parsed information appropriately.

To write a Java application that gets an SMTP notification is very similar. You’d simply use an application that uses POP3 or another e-mail protocol to sign into the account to which the notifications are sent, check whether any new messages have arrived, and if so, handle them accordingly.

The HTTP channel is perhaps the most generally useful notification delivery channel. Depending on how you implement the notification service instance (HTTP services in SQL Server 2005 are extensible) you have a number of options on how to consume it from Java. One simple method is to create a Java servlet that receives the HTTP notifications as POST commands. Alternatively, the Apache Jakarta project has a full HTTP client that you can use from within Java to build your own fully-featured client applications.

Notification Services are an extremely useful aspect of SQL Server 2005. Via notifications, you can greatly improve the responsiveness of many applications while simultaneously often significantly reducing server and network resource requirements. In this article you saw that you don’t have to write .NET applications to make use of SQL Server Notification Services. Because the services deliver notifications via standard channels such as the file system, SMTP, and HTTP, it’s easy to let Java (or other languages) take advantage of them as well.