Get Started Using SQL Server 2005 Notification Services : Page 5

Generating Notifications
After adding subscriptions to the database you can begin generating notifications from new incoming events. Remember that SQLNS generates notifications by comparing new events to the stored subscription information. More accurately, it just performs a simple T-SQL JOIN statement (the Notification Rule) between the Events and the Subscriptions table. The results of this JOIN are the notifications that SQLNS must send to the subscribers.

You define the structures of the resulting notifications through Notification Classes in the Application Definition file, in the section <NotificationClasses>, as shown in Listing 5.

After adding the Notification class, and execute nscontrol.exe to update the SQLNS instance it creates the new notification table within the application database. The last thing you need to add is a Notification Rule. The following example shows where to place the Notification Rule in the Application Definition file.

   SubscriptionClass>
      <EventRules>
         <EventRule>
            <RuleName>EvtRule</RuleName>
            <Action>
               <!&#151;Insert Notification Rule here -->
            </Action>
         </EventRule>
      </EventRules>
   </SubscriptionClass>

For clarity, I've included the Notification Rule in a separate code fragment below:

   INSERT INTO StockPriceNotifications
      (SubscriberId, DeviceName, SubscriberLocale, 
      StockCode, ExchangeCode, Price)
   SELECT s.SubscriberId, s.DeviceName, s.SubLocale, 
      e.StockCoce, e.ExchangeCode, e.Price 
   FROM StockEvt e, StockPriceSubscriptions s 
   WHERE   e.ExchangeCode = s.ExchangeCode AND 
      e.StockCode = s.StockCode AND 
      e.Price > s.TriggerVal

Be sure to escape all special characters (such as ">") because you're writing the Notification Rule in an XML file! Alternatively, you could put the Notification Rule in a stored procedure.

The preceding T-SQL statement inserts a new record into the notification table whenever SQLNS receives a matching event, then it hands all generated notifications over to the Formatter. The Formatter handles notification formatting. SQLNS ships with the XsltFormatter used in the sample application, but you're free to write your own Formatter for special cases. Using the XsltFormatter you specify an XSLT stylesheet that formats the notification. You define the Formatter in the <NotificationClass> section of the Application Definition file.. Here's an example.

   <NotificationClass>
      <ContentFormatter>
         <ClassName>
            XsltFormatter
         </ClassName>
         <Arguments>
            <Argument>
               <Name>
                  XsltBaseDirectoryPath
               </Name>
               <Value>
                  C:\Stylesheets
               </Value>
            </Argument>
            <Argument>
               <Name>
                  XsltFileName
               </Name>
               <Value>
                  Tansformation.xslt
               </Value>
            </Argument>
         </Arguments>
      </ContentFormatter>
   </NotificationClass>

After the notifications are formatted the last thing to do is send them to the subscribers. To do that, you specify a Delivery Protocol. SQLNS provides two built-in protocols, SMTP and File, which send notifications through e-mail or write them into a file. Developers often use the File protocol for testing purposes. You configure Delivery Protocols in the Instance Configuration File as follows.

   <DeliveryChannels>
      <DeliveryChannel>
         <DeliveryChannelName>
            FileChannel
         </DeliveryChannelName>
         <ProtocolName>
            File
         </ProtocolName>
         <Arguments>
            <Argument>
               <Name>
                  FileName
               </Name>
               <Value>
                  C:\Notifications.txt
               </Value>
            </Argument>
         </Arguments>
      </DeliveryChannel>
   </DeliveryChannels>

After adding the DeliveryChannel, run nscontrol.exe one last time, and the whole notification application should work. When you put a new event file in the watch directory SQLNS should write the resulting notification in the file C:\Notifications.txt.

For further information about SQLNS you should read Shyam Pather's book Microsoft SQL Server Notification Services. I hope this example has given you an overall introduction to SQLNS. And I urge you to experiment with and modify the sample configuration files to build your own applications.

Previous Page

---

• Download The Code for this Article

---