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


Get Started Using SQL Server 2005 Notification Services : Page 5

Because of the complex configuration options required to write SQL Server 2005 Notification Services (SQLNS) applications, it can be difficult to get started, but after you've learned the concepts and ideas behind SQLNS, you'll find yourself able to write great applications that are both scalable and extensible using common interfaces.


WEBINAR: On-Demand

Unleash Your DevOps Strategy by Synchronizing Application and Database Changes REGISTER >

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 &gt; 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.

Klaus Aschenbrenner is software architect and consultant for ANECON in Vienna, Austria. He helps software architects and developers design and implement enterprise solutions based on the .NET Framework and Web services. Find further information about Klaus Aschenbrenner at his weblog.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



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