RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Letting Java in on SQL Server Notifications

SQL Server's Notification Services let you instruct the database to let your applications or your users know when data changes or reaches critical thresholds. Furthermore, standard notification delivery methods ensure that notifications aren't limited to .NET or Windows clients.

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.

What You Need
This article assumes that you are using SQL Server 2005 Beta 2 and Visual Studio 2005 Beta 2. While you must have the .NET Framework 2.0 to compile and run the sample project, you don't have to have Visual Studio. Although the instructions in this article assume you're using the Visual Studio.NET 2005 IDE to compile the sample code, you can easily compile it from the command line instead. If you need to do that, Microsoft has posted instructions on how to compile it from the command line using only the .NET Framework. Check the instructions for the "Stock Quotes" sample in your SQL Server online help for details.

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 Files\Microsoft SQL Server\90\Tools\Samples directory.

Author's Note: The setup problem referred to above occurs if you try to change your current installation by installing the samples after your initial install. In that situation, SQL Server doesn't detect any configuration changes, and as a result cancels out and does nothing.

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 Files\Microsoft SQL Server 2005 Samples\Notification Services\Stock 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:

           <!-- These parameters are defined as   
           environment variables or as command line 
           arguments.  They are passed to the ADF 
           file. -->
You have to build two primary entities for a notification service: the notification instance itself, and the application that implements it.
The important setting here is <ApplicationDefinitionFilePath>, 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.

      <FieldTypeMods>not null</FieldTypeMods>
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.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date