Send a Message

Generate custom notifications out of the box with SQL Server 2000 Notification Services.

asp:cover story

LANGUAGES: XML | XSLT | C#

TECHNOLOGIES: SQL Server Notification Services | SQL Server 2000 | .NET Framework | ADO.NET

 

Send a Message

Generate custom notifications out of the box with SQL Server 2000 Notification Services.

 

By Jeanine Johnson

 

Traditional publish-subscribe (pub-sub) applications allow entities to publish data to other interested entities to distribute information on a subscription basis. Previously, service providers had to custom-develop these applications. The custom notification systems the service providers produced typically relied on similar designs and operation principles. This inspired the creation of Microsoft SQL Server Notification Services. Notification Services reduces the redundancy inherent in creating a variety of custom messaging applications by providing standard components for building such an application. At the same time, the technology increases application scalability and reliability.

 

Notification Services is a reliable, scalable platform on which to build intelligent pub-sub messaging applications. It uses SQL Server's ability to perform millions of data queries rapidly to match system events with defined subscriptions and push notifications to interested subscribers in a defined message format.

 

In this article, I'll show you how to develop an e-mail notification system that alerts subscribers of particular product sales posted to a fictional online retail store, and you'll see the design and implementation details involved in creating the system. Along the way, you'll learn about several features of Notification Services.

 

The Problem and the Solution

World Wide Importers is a fictitious online wholesale retailer that sells products ranging from posters to furniture. The inventory is inconsistent because the company buys items in bulk from various distributors to sell the products for less than standard retail prices. Thus, many of World Wide Importers' customers have requested they be sent an e-mail whenever items of interest become available or go on sale below a specified maximum price.

 

For instance, Samantha Riley is interested in purchasing a couch for less than $100, so she requests that World Wide Importers send her a message whenever a couch in this price range becomes available. Rather than develop its own notification application from scratch, World Wide Importers decides to use Notification Services as a platform for creating its custom messaging application.

 

The overall objective of World Wide Importers' notification application is to allow known customers to define subscriptions outlining the products they're interested in. Then, when items arrive that meet their established subscription criteria, customers are sent e-mails notifying them of product availability and price and providing a link to the item on www.worldwideimporters.com.

 

The design of this application is fairly simple when using Notification Services. The application consists of one instance of Notification Services that produces notifications for event-driven subscriptions. The events are entered into the system via the SQL Server event provider, which is used to detect activity in World Wide Importers' SQL Server 2000 inventory database, including price changes on existing records and the insertion of new product records. Finally, the application uses the eXtensible Stylesheet Language Transformations (XSLT) content formatter to format the generated information into the resulting notification and Simple Mail Transfer Protocol (SMTP) delivery protocol to send that notification as an e-mail to the corresponding subscriber.

 

Define the Configuration File

Notification Services relies on XML files to define the application. One configuration file exists per Notification Services instance, and one application definition file (ADF) exists for each application within that instance. World Wide Importers' messaging design consists of one Notification Services instance with one application; therefore, it has one configuration file and one ADF.

 

The configuration file contains information pertaining to the Notification Services instance and includes these features:

 

  • Instance Name field: Specifies the Notification Services instance name.
  • SQL Server Name field: Specifies the SQL Server instance that will host the Notification Services database.
  • Applications node: Specifies all the applications that will exist in the Notification Services instance; World Wide Importers has only a single application.
  • Protocols node: Specifies all the protocols available to the application; for World Wide Importers, only SMTP is specified.
  • Delivery Channels node: Specifies all the delivery channels available to the application; for World Wide Importers, only the e-mail delivery channel is defined.

 

Send Notifications by E-mail

Notification Services includes two delivery protocols, including one that provides access to SMTP that lets you define an e-mail delivery channel. This protocol and delivery combination are to route notifications to subscribers.

 

Define the delivery channel as shown within the configuration file in Figure 1.

 

<DeliveryChannel>

       <DeliveryChannelName>Email</DeliveryChannelName>

       <ProtocolName>SMTP</ProtocolName>

       <Arguments>

            <Argument>

               <Name>SmtpServer</Name>

               <Value>local</Value>

             </Argument>

            <Argument>

               <Name>BodyEncoding</Name>

     <Value>utf-16</Value>

   </Argument>

 </Arguments>

</DeliveryChannel>

Figure 1. The definition of the delivery channel specifies its name, Email; the protocol it uses, SMTP; and any arguments specific to that protocol. For SMTP, you need the server location and body encoding.

 

Within the notification class, include the protocol information shown in Figure 2. This XML fragment is the way you describe the delivery channel for your notifications. In this example I use SMTP, but you also could use Instant Message or Short Message Service, which Notification Services also supports out of the box.

 

<Protocol>

 <ProtocolName>SMTP</ProtocolName>

 <Fields>

  <Field>

    <FieldName>Subject</FieldName>

    <SqlExpression>ItemName + &apos; is now on sale for

     &apos; + ItemPrice</SqlExpression>

  </Field>

  <Field>

    <FieldName>From</FieldName>

    <SqlExpression>&apos;[email protected]

    &apos;</SqlExpression>

  </Field>

  <Field>

    <FieldName>To</FieldName>

    <SqlExpression>DeviceAddress</SqlExpression>

  </Field>

  <Field>

   <FieldName>Priority</FieldName>

   <SqlExpression>&apos;Normal&apos;</SqlExpression>

  </Field>

  <Field>

   <FieldName>BodyFormat</FieldName>

   <SqlExpression>&apos;html&apos;</SqlExpression>

   </Field>

 </Fields>        

 <ProtocolExecutionSettings>

  <RetrySchedule>

    <RetryDelay>P0DT00H05M00S</RetryDelay>

  </RetrySchedule>

  <FailuresBeforeAbort>3</FailuresBeforeAbort>

  <MulticastRecipientLimit>10</MulticastRecipientLimit>

  <WorkItemTimeout>P0DT00H10M00S</WorkItemTimeout>

 </ProtocolExecutionSettings>

</Protocol>

Figure 2. This code describes the fields included for the particular protocol you use for your delivery. In this case, you see SMTP fields such as From and To in the definition.

 

The entire configuration file for World Wide Importers is included with the sample application for this article; see the Download box at the beginning of the article for details.

 

Create an ADF and Event Class

Each application within a Notification Services instance is defined by one ADF. The ADF defines the event, subscription, and notification classes; indicates the event provider used; and establishes the generator, distributor, and other application settings. For World Wide Importers, only one application is defined for generating product alerts.

 

The Event class defines the events you use to trigger the system. For World Wide Importers' Product Alert application, you need only two pieces of information to construct an event: the item's name and price (see Figure 3).

 

<EventClassName>ProductEvents</EventClassName>

<Schema>

  <Field>

    <FieldName>ItemName</FieldName>

    <FieldType>nvarchar(25)</FieldType>

    <FieldTypeMods>not null</FieldTypeMods>

  </Field>

  <Field>

    <FieldName>ItemPrice</FieldName>

    <FieldType>decimal(18,2)</FieldType>

    <FieldTypeMods>null</FieldTypeMods>

  </Field>

</Schema>

Figure 3. Here the XML defines the Event class, which consists of the data found in each event. In this case, the item name and price are the only data extracted from the event for the corresponding notifications. Thus, the data is defined as fields within the schema section of the Event class definition.

 

In addition to the Event class, there is an event-chronicle table (see Figure 4), which you can use to track event-related information. This helps you differentiate valuable event data from unnecessary information so duplicate notifications aren't generated. The event-chronicle table is used to track the lowest price listed for each product. It does this by receiving updates and new product additions via the event-chronicle rule defined in the Subscription class (refer to the Event Rule within the Subscription class section of the ADF). Thus, subscribers to World Wide Importers are notified only when a product becomes listed at a price lower than its current listing.

 

<Chronicle>

<ChronicleName>ProductEventsChron</ChronicleName>

  <SqlSchema>

    <SqlStatement>            

      CREATE TABLE ProductEventsChron

       (

        ItemName    nvarchar(25),

        ItemPrice    decimal(18,2)

      )

    </SqlStatement>

  </SqlSchema>

</Chronicle>

Figure 4. As part of the Event class, you can define an event chronicle to track information related to the events. In this case, the event chronicle tracks each item and its corresponding price. As indicated by the Event Rule in the ADF's Subscription class, it maintains a table of the lowest prices associated with each item to eliminate unnecessary or redundant notifications.

 

The SQL Event Provider

Notification Services includes a SQL Server event provider, which you can use to enter events into the system based on activity within a specified SQL Server 2000 database. Thus, this packaged component is perfect for monitoring the activity of the products within our fictitious store because they are inventoried within a SQL Server database.

 

To include the SQL Server event provider, add the information to the notification application's ADF (see Figure 5). This definition includes the name used to reference the event provider, the class used to instantiate it, the frequency (interval) in which it will be activated to search for new events, as well as arguments containing both the SQL queries used to extract events (EventsQuery) and the Event class associated with the event provider.

 

<HostedProvider>

  <ProviderName>SQLInventory</ProviderName>

      <ClassName>SQLProvider</ClassName>

      <SystemName>%_NSSystem_%</SystemName>

      <Schedule>

        <Interval>P0DT00H00M60S</Interval>

      </Schedule>

      <Arguments>

        <Argument>

          <Name>EventsQuery</Name>

          <Value>USE Inventory SELECT ItemName, ItemPrice

           FROM Products</Value>

        </Argument>

        <Argument>

          <Name>EventClassName</Name>

          <Value>ProductEvents</Value>

        </Argument>

      </Arguments>

</HostedProvider>

Figure 5. You specify that you want to use the SQL Server provider, which listens for changes in the SQL Server system. The SQL query you pass as part of the XML gives the provider the query it needs to monitor the event source.

 

Create a Subscription Class

Notification Services offers two subscription types: event-driven subscriptions and scheduled subscriptions. World Wide Importers' Product Alert application leverages only the event-driven subscriptions.

 

The subscription needs to contain the item name and maximum allowable price of the item the subscriber is interested in monitoring, as well as the name of the device to be used in contacting the subscriber and his or her locale (language) information. Figure 6 shows the subscription.

 

<SubscriptionClassName>ProductSubscriptions

</SubscriptionClassName>

<Schema>

  <Field>

    <FieldName>DeviceName</FieldName>

    <FieldType>nvarchar(255)</FieldType>

    <FieldTypeMods>not null</FieldTypeMods>

  </Field>

  <Field>

    <FieldName>SubscriberLocale</FieldName>

    <FieldType>nvarchar(10)</FieldType>

    <FieldTypeMods>not null</FieldTypeMods>

  </Field>  

  <Field>

    <FieldName>ItemName</FieldName>

    <FieldType>nvarchar(25)</FieldType>

    <FieldTypeMods>not null</FieldTypeMods>

  </Field>

  <Field>

    <FieldName>ItemMaxPrice</FieldName>

    <FieldType>decimal(18,2)</FieldType>

    <FieldTypeMods>not null</FieldTypeMods>

  </Field>

</Schema>

Figure 6. Here you can see the subscription information for the SQL Server you are monitoring. Your users can use the subscription schema to define their subscriptions.

 

Next, you need an event rule to use not only to maintain the event-chronicle table with the item and its corresponding lowest price, but also to call the Notify function that will generate the notifications. You can find this rule in the sample application's Subscription class. The key thing is that the rule is a simple T-SQL query that looks for items that have a maximum price less than the price you set as the threshold.

 

Because World Wide Importers' application provides only event-driven notifications, the generator processes the subscription-event rule, which joins the event-chronicle data with subscription data based on the event ItemName value that's equivalent to the ItemName value in the subscription, and the event ItemPrice value equal to or less than the ItemMaxValue value in the subscription.

 

For each match found during the generator's processing of this rule, a notification is generated.

 

Establish Generator and Distributor Settings

Notification Services allows the major application components (event provider, generator, and distributor) to reside in a distributed environment to optimize system performance. For World Wide Importers' application, all the components reside on the same machine. Therefore, the generator and distributor settings both use the same variable to identify the host computer. The notification class defines the information to include in each notification. For World Wide Importers' Product Alert notifications, the only data included is the item name and corresponding price (see Figure 7).

 

<NotificationClassName>ProductNotifications

</NotificationClassName>

<Schema>

  <Fields>

    <Field>

      <FieldName>ItemName</FieldName>

      <FieldType>nvarchar(25)</FieldType>

    </Field>

    <Field>

      <FieldName>ItemPrice</FieldName>

      <FieldType>decimal(18,2)</FieldType>

    </Field>

  </Fields>

</Schema>

Figure 7. This Notification class defines the fields that are returned to Notification Services when an event meets or exceeds your defined thresholds.

 

Then, this information is formatted using the stylesheet developed for the application, which is referenced via the XSLT content-formatter component of Notification Services and routed to subscribers using the e-mail delivery channel based on the SMTP delivery protocol.

 

Also defined within the ADF's notification class node is the content formatter used to format the notification data prior to distributing it to subscribers. The XSLT content formatter is used for World Wide Importers' Product Alert application (see Figure 8).

 

<ContentFormatter>

  <ClassName>XsltFormatter</ClassName>

  <Arguments>

    <Argument>

       <Name>XsltBaseDirectoryPath</Name>

      <Value>%_BaseDirectoryPath_%</Value>

    </Argument>

    <Argument>

      <Name>XsltFileName</Name>

      <Value>WorldWideImportersAlerts.xslt</Value>

    </Argument>

  </Arguments>

</ContentFormatter>

Figure 8. Here's the built-in XSLT content formatter included with Notification Services. Your XSLT takes the fields you define in the Notification class and applies the transformation defined in the corresponding XSLT file to format these fields into a readable e-mail you can send to the subscriber.

 

The XSLT referenced is included in the sample download for this article.

 

Establish the Application Execution Settings

Finally, you need to set the system's quantum duration for the generator component of Notification Services. A quantum is used to denote a unit of time for Notification Services components. In this case, set the quantum duration for the generator to five minutes to balance the application's speed against the resources consumed during processing on the system where the application resides. You do this in the Application Execution Settings section of the ADF.

 

Now the Notification Services application is configured to push notifications as e-mails. You can download the completed configuration file and ADF.

 

In practice, the developer of a Notification Services application is responsible for establishing a method for interested customers (subscribers) to create subscriptions. You do this through a Web or Windows application that uses the Notification Service Subscriber and Subscription classes to enter this information into the system and maintain the link between subscribers and the subscriptions they establish.

 

For this application, assume this subscriber and subscription information has been established already and inserted into the system. You can download the code that accomplishes this.

 

Get to Know NSControl

Notification Services provides the NSControl utility to create and modify notification applications. This tool lets you create and enable the applications defined by the configuration file and ADFs they design.

 

Run this command to create the databases that correspond to those described by the ADF and configuration file:

 

NSControl create -in < path>\WorldWideImportersConfig.xml

sqlserver=<Sql_machine_name>

basedirectorypath=<fully_qualified_path>

nshost=<NS_machine_name>

 

When you execute this command, two databases are created: WorldWideImportersInstanceNSMain (the instance database), and WorldWideImportersInstanceWorldWideImportersCustomerAlertApp (the application database). To run the Notification Services application, you must define it as a service within the Windows operating system and execute under a Windows account with appropriate privileges. For this application, the windows account used needs to be in the "db_owner" role for both the instance and application databases.

 

This windows account also must run Windows services. You can obtain this permission from the Local Security Policies administration tool.

 

Finally, this windows account needs permissions to run the extended stored procedures for Notification Services. These permissions are granted by running this SQL statement:

 

USE master EXEC sp_grantdbaccess '<username>'

 

You also need to run this executable from the bin directory of your Notification Services installation:

 

grantxpexec <username> -E -s <NS_machine_name>

 

Now you can register this service under the established account by using this command line:

 

NSControl register -name WorldWideImportersInstance

-server <NS_machine_name>

-serviceusername <username>

-servicepassword <password> -service

 

The final step in preparing World Wide Importers' Product Alert application for execution is to enable it. You do this using this NSControl command:

 

NSControl enable -name WorldWideImportersInstance

 

Run the Notification Services App

Now you've created a Notification Services application for World Wide Importers. It contains two databases and is on the verge of execution. Once you start the application, you need to add some events to the system to trigger the generation of notifications. Before executing the notification service, you need to create the Inventory database the application's SQL Event Provider will monitor. This database resides on the same system as the notification service, and you can create it by executing a script you can download.

 

Additionally, ensure that the Windows account the notification service runs under also has db_owner permissions for this newly created database.

 

Now you're ready to run the notification application. Start the Windows service associated with it using this command:

 

net start NS$WorldWideImportersInstance

 

Now the Notification Services application is running on the system. To start generating notifications, you need to insert some events, but first you need to stop the SMTP service on the machine to ensure the notifications remain on the system:

 

net stop smtpsvc

 

Now, the notifications are dropped into the Inetpub\mailroot\Pickup directory on the Windows 2000 or Windows XP computer.

 

To try out the notification application, you need to insert new items into the Products table of the Inventory database for World Wide Importers by executing a script you can download.

 

Because the Inventory table is monitored by your notification application's SQL Server event provider, the notification application should generate notifications for your subscribers.

 

The system processes the events you inserted by matching their data with the subscription and subscriber information and generates the corresponding notification packets. Then, it formats these notifications via the XSLT content formatter and distributes them via SMTP using the e-mail delivery channel. When the e-mail delivery channel hands off the notification packets to the external delivery system (such as a mail server), they are routed to the corresponding subscriber.

 

To view the notifications the sample World Wide Importers' Product Alert application produces, you can go to your system's Inetpub\mailroot\Pickup directory and view the text-file notifications generated.

 

Although this article demonstrates one solution using components included within Notification Services, you can implement many other solutions quickly to solve a variety of traditional pub-sub problems. Additionally, there are many ways to enhance the application implemented in this article, including hosting the notification application on a computer separate from the inventory database; creating an ASP.NET Web site where authenticated customers can sign up for the notification service and manage all their subscriptions; and adding scheduled subscriptions that send a single e-mail daily, weekly, or monthly with information on all the items of interest to the subscriber.

 

The sample code referenced in this article is available for download.

 

Jeanine Johnson is a software design engineer for Microsoft, focusing on ensuring Notification Services has a secure architecture that enables application developers to integrate security into their applications. In addition to her work on Notification Services, she aided in the development of Microsoft's .NET Framework library and Visual Studio 6. E-mail Jeanine at mailto:[email protected]; please include "asp.netPRO magazine article" in the subject header.

 

Tell us what you think! Please send any comments about this article to [email protected]. Please include the article title and author.

 

 

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish