Export (0) Print
Expand All
Creating Audit Tables, Invoking COM Objects, and More
Exception-handling Techniques
Exploring SQL Server Triggers: Part 2
Deliver User-Friendly Reports from Your Application with SQL Server Reporting Services
SQL Server: Display Your Data Your Way with Custom Renderers for Reporting Services
Updating Data in Linked Servers, Information Schema Views, and More
XML Features in SQL Server 2000
Expand Minimize

SmallLibrary Sample: Incorporating Notification Services

SQL Server 2000
 

David Willson
Microsoft Corporation

November 2002

Applies To:
   Microsoft® SQL Server 2000
   Microsoft® SQL Server Notification Services
   Microsoft® Visual Studio® .NET

Summary: Demonstrates how adding Microsoft SQL Server Notification Services as a feature can enhance an existing application. Gives an overview of an existing, simplified library management Web application, and the process of designing the notification service features for the library. (23 printed pages)

Download Smalllibrarysetup.msi.

Download the SmallLibrarySetup.msi sample file. (782 KB)

Contents

Introduction
Designing a Notification Service
Entity Relationships and Database Schema
Modifications to the Web Application
Preserving Application Architecture
Developing Notification Services Metadata
The Notify Function
Defining Notification Class Schema Fields
The Notification Content Formatter
Setting Notification Protocols for E-Mail
Defining Subscription Class Schema Fields
Defining a Match Rule
Compiling the ADF and Configuration Files
The Notification Services API
Creating a Subscriber
Updating a Device Address
Testing Notification Services
Other Notification Services Resources
Conclusion

Introduction

Microsoft® SQL Server Notification Services is a flexible notification sub-system that can be added to an existing application without much effort. The MSDN SmallLibrary sample is an example of how an existing application can be enhanced by adding Notification Services as a feature.

This article will give you an overview of the existing system, a simplified library management system, and the process of designing the notification service features for the library. Once the design process is complete, the features of the notification service are defined in the Notification Services metadata files. Finally, the metadata files are compiled and the system is unit-tested using a cyclical manual process and the event manager.

The Notification Services developer must be familiar with command-line syntax and XML. The developer must also understand Transact-SQL join logic, Microsoft SQL Server objects, and the cause of specific database error conditions and Microsoft SQL Server error messages.

Designing a Notification Service

Notification Services is designed to have little impact on the code base of existing systems. It includes a managed code API that is easy to use and is defined and managed by metadata expressed in XML. All that is needed to design a notification service is an understanding of the components of a notification and where these components need to be managed by the existing software. This section begins with a high-level overview of the existing system, identifying where the feature will be installed. The next part describes the more complicated work of defining the notification service metadata files and management logic.

At a very high level, a notification is the result of a subscription and an event that takes place. In the SmallLibrary system, there are two types of notifications that we propose to develop. The first is a notice that a book is overdue. The second is a hold notice. A hold notice is sent to a patron when a book the patron has reserved becomes available.

These two subscriptions differ slightly. Hold notices are event-based—they are generated when books are checked in, which are events. Overdue notices are generated when books become overdue. There is no event that takes place when a patron's loan expires, so the overdue notice subscription is schedule-based.

Using Notification Services terminology, library patrons are subscribers. Subscribers are the entities that receive notifications on some device.

SmallLibrary Sample Overview

Incorporating hold notices and overdue notices into the SmallLibrary sample using Notification Services requires few changes. This section is an overview of the SmallLibrary sample before and after applying Notification Services.

Entity Relationships and Database Schema

The SmallLibrary system consisted of six entities. There is a patron (the person checking out books), the volume (the book checked out), and the title (of the book). There may be more than one book with the same title, thus the distinction. The other three entities are patron contracts with the library. The loan contract represents a volume that the patron has checked out. The hold contract represents a book that has just returned to the library that is reserved for the exclusive use of one patron. The reservation contract represents a patron's request to create a hold contract when a volume of a certain title becomes available. These entities were mapped to tables in a Microsoft SQL Server database as shown in Figure 1 below. Note that the arrows represent the direction of entity relationship. For example, one patron may have many loans.

Aa902653.smalllibrarysample_fig1(en-us,SQL.80).gif

Figure 1. SmallLibrary sample application database schema

No changes to the SmallLibrary database were required to incorporate Notification Services into the system.

Note   Notification Services uses the data type NVARCHAR(255) for SubscriberId. To accommodate Notification Services, we could have converted the Id column of the Patrons table to the NVARCHAR(255) data type. Instead, it is more efficient to use string conversion, such as CONVERT(NVARCHAR(255), [Id]) for Transact-SQL code, CStr(iPatronId) for Microsoft® Visual Basic® .NET code, or Convert.ToString(patronId) for C# code. Generally, numeric identifiers are more efficient than string identifiers, while string identifiers are more accommodating.

Modifications to the Web Application

The existing Web application for the SmallLibrary system is modified slightly to include four new functions, as shown in the state diagram below. Four modules collectively include only a few lines of code to interact with the Notification Services feature.

Aa902653.smalllibrarysample_fig2(en-us,SQL.80).gif

Figure 2. State diagram of the SmallLibrary Web application, including notification services actions that have been added to the existing system.

Preserving Application Architecture

The layered development architecture model is the best way to develop a scalable Web application. Logically partitioning the application into portable modules will allow the application to be arranged in the most efficient physical configuration for the current demand. In the current situation, the demand is low for the SmallLibrary system. The entire system, even the data sources, is located on the same server. Were demand to suddenly pick up at the library, perhaps the Business Logic and Data Access layer modules could be distributed to a farm of servers to carry the increased load.

Preserving this logical partitioning as features are added preserves scalability and maintenance of the greater application. The new logic that was added to manage Notification Services subscribers and subscriptions was incorporated into the SmallLibrary Microsoft® Visual Studio® solution file as a separate Business Logic Layer project, "NotificationLogic", as shown in the right-side column of Figure 3.

Aa902653.smalllibrarysample_fig3(en-us,SQL.80).gif

Figure 3. Architecture of the SmallLibrary sample

Incorporating Notification Services as a feature does not impact the existing SmallLibrary system in any other way.

Developing Notification Services Metadata

Once the notification service is designed, the information describing each component of the service is incorporated into metadata files. The notification classes and the subscription classes, for example, are two such components. Think of the notification classes as attributes that describe the content of a message. In the case of the overdue notice, the notification class includes attributes such as the due date and the title of the book. Subscription classes are attributes that describe to whom a message is delivered, preferred content, and scheduling. The metadata organizes all information about a notification service, including subscriptions, delivery channels, scheduled events, performance monitoring, and maintenance.

The Metadata Files

Microsoft Notification Services is a feature-rich product that uses XML as its design interface. Two XML schemas have been created to describe the metadata for all Notification Services projects. These are used to create the application definition file and the configuration file that define a Notification Services implementation.

First of interest is the application definition file (ADF). It describes the attributes of a Notification Services application. These are elements such as the subscriber, events, notification content, schedules, message formats, and distribution channels for a notification. The ADF may also include performance-monitoring content and clean-up schedules for a Notification Services application.

The configuration file describes the operating system service that is created to manage the set of Notification Services applications that use a common security context. This service is known as a Notification Services instance.

In the MSDN SmallLibrary sample application, there is only one Notification Services instance. Within that Notification Services instance, there is only one Notification Services application.

Finally, there is the NsControl.exe command-line program that is used to compile the metadata described in the configuration file and in the application definition files. The NsControl.exe program has commands that are used to create, register, and enable the notification service. It also includes commands that are used for testing and maintenance of the installed Notification Service instance.

Where to Build the Metadata

XML is the language of both the ADF and the configuration file, so choose your favorite editor. Microsoft Visual Studio .NET offers an excellent platform for developing XML with color-coded elements, attributes, values, and comments. The most convenient feature is provided by Microsoft® IntelliSense®: sub-elements of a node will be suggested when the XML schema file is included in the project. This feature set speeds development and reduces the potential for errors. Here are a few simple steps to guide you through the process of setting this up:

  1. Add a C# or C++ Windows Application project to your solution. (This process will not require C# or C++ programming, but you may choose to include a custom script of your own design that will use the NsControl.exe command-line program to create, register, and enable your Notification Services instance when you build the project. Just configure the start application and command-line arguments in the project property pages.)
  2. Remove the three automatically generated files named App.ico, AssemblyInfo.cs, and Form1.cs from the project.
  3. Add from the directory where Microsoft Notification Services was installed the two XML schema files ApplicationDefinitionFileSchema.xsd and ConfiguratonFileSchema.xsd.
  4. Copy the code below to a notepad, save the file as Adf.xml, and then include that file in your project.
    <?xml version="1.0" encoding="utf-8"?>
    <!-- Use this comment block to describe your NS Application -->
    <Application 
       xmlns:xsd="http://www.w3.org/2001/XMLSchema"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns="http://www.microsoft.com/MicrosoftNotificationServices
       /ApplicationDefinitionFileSchema">
    </Application>
    
  5. Copy the code below to a notepad, save the file as Config.xml, and then include that file in your project.
    <?xml version="1.0" encoding="utf-8"?>
    <!-- Use this comment block to describe your NS Instance -->
    <NotificationServicesInstance
       xmlns:xsd="http://www.w3.org/2001/XMLSchema"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns="http://www.microsoft.com/MicrosoftNotificationServices
       /ConfigurationFileSchema">
    </NotificationServicesInstance>
    

Defining an Event Class

The scenario where a message should be sent in response to an action describes a Notification Services event. Notification Services event classes describe the attributes of the event. For example, if the ADF is being planned for sporting event notifications, the file might be designed to include an event for the end of the game. Perhaps the attributes for the end of the game would be the names of the teams and the final score.

When an event takes place, the attributes of the event are recorded in the Notification Services databases and provided to the development interface in the form of a view that has the same name as the event class. This event view is often referenced by match rules in other classes. This means that as you begin to develop your ADF, it makes sense to define the fields of the event class schema before defining the subscription or the notification classes related to the event.

There is only one event class defined in the SmallLibrary sample. When a patron checks in a book, other patrons may be waiting to check out the book. The patron that has been waiting the longest will have the exclusive right to check out the book for a short while (the book is "on hold"). Shortly after a book has been placed on hold, a notice should be delivered to the patron that has been waiting. The attributes of this event are used to describe the book that is on hold and the patron that is waiting for the book.

What attributes describe the hold event? Actually, all that is needed to describe a hold event is the unique numeric identifier from the primary key column of the Hold table, which is in the SmallLibrary database. The SmallLibrary database is highly normalized. This means that the Title table contains columns that describe just the title. The Hold table columns describe only the hold contract. The Hold table describes nothing more about the contract, other than to include a numeric reference to related information in another table. So in a query, join clauses can be introduced to derive other related content, such as the title of the book that is in the hold contract.

Should the hold identifier be the only attribute of a hold event? Probably not, but the choice is up to the developer. In the code sample below, three additional schema fields—SubscriberId, TitleId, and Title—are added to the HoldEvent event class schema. If you look ahead to the definition of the HoldSubscription subscription class, the HoldEvent event class is referenced in a query. If these three fields were not included, it would be necessary to join to three additional tables in the query.

<EventClass>
  <EventClassName>HoldEvent</EventClassName> 
  <Schema>
    <Field>
      <FieldName>SubscriberId</FieldName> 
      <FieldType>NVARCHAR(255)</FieldType> 
    </Field>
    <Field>
      <FieldName>HoldId</FieldName> 
      <FieldType>INT</FieldType> 
    </Field>
    <Field>
      <FieldName>TitleId</FieldName> 
      <FieldType>INT</FieldType> 
    </Field>
    <Field>
      <FieldName>Title</FieldName> 
      <FieldType>NVARCHAR(100)</FieldType> 
    </Field>
  </Schema>
</EventClass>

Defining a Notification Class

For Notification Services, the entity that receives the notification is known as the subscriber. The notification class defines the attributes of the message that is sent to the subscriber. Similar to the event class, which describes the columns of a view that is created in the Notification Services data sources, the notification class describes a database function that is created.

The Notify Function

The fields defined in the notification class become parameters of a function with the name, dbo.<notificationclassname>Notify. This means that if you choose the notification class name of EndOfGameNotice, the function name will be dbo.EndOfGameNoticeNotify.

When the metadata class is compiled into the Notification Services instance, the Notify function that is created will include SubscriberId, DeviceName, and SubscriberLocale as the first three parameters (and in that order). Any fields defined in the notification class will be appended as parameters to the end of the function, in the order that they are defined in the class.

Defining Notification Class Schema Fields

The schema fields that are defined in the notification class represent the attributes of the message that will be sent to the subscriber. Typically, these will be set attributes of an event in a Notify function.

In the SmallLibrary sample, there are two notification classes. One notification class is for an overdue notice. The OverdueNotice class has three fields defined: LoanId, Title, and Due. Ultimately, only the Title and Due fields are used by the content formatter to form the message. The LoanId column could be used to create a link in the message to the check-in page for that book in the Web application.

The Notification Content Formatter

The content formatter is used to transform elements of data into a readable message. The content formatter element describes the location of the file used to transform the notification.

Notice the maintenance advantage in this code sample of using the _BaseDirectoryPath_ parameter to describe the XsltBaseDirectoryPath. This particular parameter was defined in the configuration file. There could be many notifications classes defined in a single ADF, so using parameters like this makes code maintenance easier.

<ContentFormatter>
  <ClassName>XsltFormatter</ClassName> 
  <Arguments>
    <Argument>
      <Name>XsltBaseDirectoryPath</Name> 
      <Value>%_BaseDirectoryPath_%\NotificationService\</Value> 
    </Argument>
    <Argument>
      <Name>XsltFileName</Name> 
      <Value>HoldNotice.xslt</Value> 
    </Argument>
  </Arguments>
</ContentFormatter>

In the SmallLibrary sample, XSLT files are used to transform the notification into a readable HTML message. Although the HoldId field is available, only the Title field of the HoldNotice notification class is used in the HoldNotice notification message. The following is the XSLT file used to convert the title into a readable message.

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:template match="notifications">
    <HTML>
    <BODY>
      <xsl:apply-templates />
      <I>This message was generated using
      <BR/>Microsoft SQL Server Notification Services</I><BR/><BR/>
    </BODY>
    </HTML>
  </xsl:template>
  <xsl:template match="notification">
    <P>The book <B><xsl:value-of select="Title"/></B>
    <BR/>is now available for you to check out
    <BR/>at the MSDN smallLibrary Sample Application
    </P>
  </xsl:template>
</xsl:stylesheet>

Setting Notification Protocols for E-Mail

Each subscriber can be associated with many devices. In Notification Services, a device is an end-point for notifications. For example, subscriber Joe may have a device associated with his work e-mail address, one associated with his home e-mail address, and a third device associated with his SMS-enabled phone.

Devices are associated with delivery channels. Using the same example, both e-mail devices would be associated with an e-mail channel, whereas the phone device would be associated with a phone channel.

Finally, each delivery channel sends notifications over some protocol. Again, using this example, the e-mail channel would use the SMTP protocol, whereas the phone channel would use the SMS protocol.

Protocols generally have some configurable attributes. For example, in the case of the SMTP protocol, you might specify whether e-mail messages should be sent as plain text or as HTML (this information corresponds to an SMTP header). These attributes are defined in two places: 1) in the configuration XML file, under the delivery channel definition; and 2) in the ADF, under the notification-class definition. Examples of this follow.

In the SmallLibrary sample, there is only one delivery channel identified in the configuration metadata. It is named "EmailChannel" with the named protocol "SMTP."

<DeliveryChannel>
  <DeliveryChannelName>EmailChannel</DeliveryChannelName> 
  <ProtocolName>SMTP</ProtocolName> 
</DeliveryChannel>

Therefore, there is at most one protocol that could be used by a notification. The final stage of defining a notification class involves setting the properties for the supported protocols. The SMTP protocol for e-mail has three required fields: Subject, From, and To. The DeviceAddress property used for the To column is provided by the service as the address for the device of the given subscriber. The Priority field is optional. The BodyFormat field is also optional, but it is set to "text" as a default. If the message is formed in HTML, as it is in the SmallLibrary sample, explicitly set the field to "html" as shown in the code below.

<Protocol>
  <ProtocolName>SMTP</ProtocolName> 
  <Fields>
    <Field>
      <FieldName>Subject</FieldName> 
      <SqlExpression>'Overdue Notice'</SqlExpression> 
    </Field>
    <Field>
      <FieldName>From</FieldName> 
      <SqlExpression>'someone@example.com'</SqlExpression> 
    </Field>
    <Field>
      <FieldName>To</FieldName> 
      <SqlExpression>DeviceAddress</SqlExpression> 
    </Field>
    <Field>
      <FieldName>BodyFormat</FieldName> 
      <SqlExpression>'html'</SqlExpression> 
    </Field>
  </Fields>
</Protocol>

The window in Figure 4 below shows a hold notification. This is the product of defining a notification class. The book title in the body of the message is provided by the Title field, which is defined in the HoldNotice notification element of the ADF. It is formatted by the hold notice XSLT file, referenced in the content formatter sub-element of the notification class, and delivered using the SMTP protocol defined in the notification class.

Aa902653.smalllibrarysample_fig4(en-us,SQL.80).gif

Figure 4. The U.S. English hold notice e-mail message is one product of the SmallLibrary notification service.

Defining a Subscription Class

The subscription describes a contract between a subscriber and the notification service to receive one kind of notification. The attributes of the subscription class include a description, provided by the subscriber, of the content that the subscriber is interested in. These attributes are used to create a view that can be joined to event information in what is known as a match rule. The name of the table that is created will have the same name as the subscription class.

Defining Subscription Class Schema Fields

Typically, a subscription class schema will include at least two fields. These are the subscriber's device and language. The subscriber chooses a preferred language and provides addresses for his or her communication devices independent from the subscription process. However, the subscription does need to include the context of which device and which language should be used when the notification is created and delivered.

Note   If you choose to include fields for device and language, choose the data type NVARCHAR(255) for the device, and data type NVARCHAR(10) for the language.

The schema should also include any content that will be used in the match rule, which filters out all but the information the subscriber would like to see in the notification. Using the sporting event notification service as an example one more time: If the subscription provides the final scores of all the professional games in a city, the city would need to be a field included in the subscription class schema fields.

In the SmallLibrary sample, the hold notice subscription is created for patrons (subscribers) that have reserved a book. When the requested book becomes available, a message is sent to the patron stating that the book is available to check out. At the time that the book is reserved, only the TitleId—the number that identifies the title of the book—can be provided by the subscriber. The following code describes the hold-notice subscription-class schema fields:

<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>TitleId</FieldName> 
    <FieldType>INT</FieldType> 
    <FieldTypeMods>NOT NULL</FieldTypeMods> 
  </Field>
</Schema>

Defining a Match Rule

The match rule uses join logic and the notify function, which is defined in the notification class of the ADF, to produce a set of records (a notification batch) that will be distributed to the various subscribers. The following query is a match rule used in the hold subscription class to create hold-notice notifications.

<EventRule>
  <RuleName>HoldNoticeRule</RuleName> 
  <Action>
    SELECT dbo.HoldNoticeNotify(
      s.SubscriberId
      ,s.DeviceName
      ,s.SubscriberLocale
      ,e.HoldId
      ,e.Title) 
    FROM HoldSubscription s JOIN HoldEvent e 
      ON s.SubscriberId = e.SubscriberId 
      JOIN SmallLibrary.dbo.Hold h 
        ON e.HoldId = h.[Id] 
    WHERE e.TitleId = s.TitleId 
      AND h.Active = 1
  </Action> 
  <EventClassName>HoldEvent</EventClassName> 
</EventRule>

Query Analysis: Origin of the Three Tables

Let's analyze this hold-notice rule more closely. There are three tables that participate in the join clause. The first table is the HoldSubscription view, which is located in the Notification Services data sources. It is defined in the HoldSubscription subscription class of the ADF, and set when the patron reserves a book. (The Web application uses the managed code Notification Services API to install the subscription information.)

The second table is the HoldEvent view. Notice that this match rule is recorded inside an EventRule node, and the value of the EventClassName node is set to the HoldEvent event class. This means that the match rule query is executed in response to a hold event. The columns of the HoldEvent view are recorded in the Notification Services data sources as metadata, and they are defined in the HoldEvent event class of the ADF.

The third table in the join clause is the SmallLibrary.dbo.Hold table. Since the query is not executed in the SmallLibrary database, there is an external table reference prefix applied to the name of the table.

Query Analysis: Join Logic

The Hold table is restricted to the set of reserved books with Active = 1, because there may have been other situations (still recorded in the table with Active = 0) where the user placed the same book on hold. Both the subscriber identifier and the title identifier must match for the HoldSubscripton table and the HoldEvent table join. The hold identifier is used to restrict the subscription content to only those hold subscriptions that are included in the current hold event. Therefore, there is a join between the HoldEvent view and the Hold table.

Query Analysis: The Selection Set

The match rule query assembles five columns into the parameters of the dbo.HoldNoticeNotify function. The first three columns of this function are required by all similar notification functions. The last two of five parameters of this function are defined in the HoldNotice notification class in the ADF.

Note   The match rule is expressed in Transact-SQL, but it is written in the ADF as the value of an XML node. If XML-reserved characters such as ">" and "<" are used, they may need to be XML encoded. For example: "WHERE Age >= 18" will need to be written "WHERE Age &gt;= 18".

Defining a Scheduled Event

There is no event that is caused by the MSDN SmallLibrary Web application that marks the point in time when a book becomes overdue. An event must be scheduled so that the overdue notice match rule can be executed.

There are two important distinctions to notice between the hold subscription mentioned above and the overdue subscription. One distinction is subtle; the overdue subscription class uses a ScheduledRule node instead of an EventRule node. The more interesting distinction between the two appears in the code that creates the overdue notice subscription for the patron.

An overdue notice subscription is created each time a patron checks out a book. The patron may have checked out the same book at an earlier time, so it is important to distinguish these events. In the SmallLibrary sample, a loan describes one instance of checking out a book. The loan identifier is recorded as part of the subscription.

More important is the scheduling. Two properties of the subscription object must be set before the subscription object is added to the Notification Services data sources. In the following example, oSubscription represents a subscription object. The subscription is being set to run once daily beginning at 18:19:20 UTC on August 30, 2002.

oSubscription.ScheduleRecurrence = "FREQ=DAILY;"  
oSubscription.ScheduleStart = "20020830T181920Z"

ScheduleRecurrence is highly configurable for recurrence intervals greater than one day. It is not anticipated that there will be many notification systems designed that are scheduled to produce scheduled notifications on an hourly basis, so there is no setting in the current version for hourly recurrence. However, if this is required for your system, one simple solution might be to create 24 subscriptions per subscriber each with the ScheduleRecurrence property set to "FREQ=DAILY;". Just modify the ScheduleStart property for each of the subscriptions to represent the hour intervals of the starting day. Another possible solution that conserves subscriptions: Use the match rule in the subscription class to modify the subscription's schedule, bumping it up one hour each time the rule executes.

Using Configuration File Parameters

Setting up parameters in the configuration file involves a little bit of work, but using them is a great way to enhance maintenance of the Notification Services metadata. Consider that when parameters are not used, server names, directory paths, and other dynamic content will be recorded throughout the metadata files. This may be seem to be good enough for single-server installations or for the quick testing of a feature, but it is a poor choice for long-term maintenance. It is a nuisance to repeat a long directory path. Worse, searching and replacing all instances of a path or server name for another in otherwise functional code is a process that is prone to error.

Once the metadata is defined, the first step of preparing to build the Notification Services using the metadata is to organize the dynamic content into a set of parameters. These parameters are defined in the configuration file. Later, when the NsControl.exe program is used to create or update the notification service, the set of parameters defined in the configuration file must be included in the command.

In the SmallLibrary sample, there are three parameters declared in the configuration file that help organize dynamic content. The SqlServer parameter is used to describe the server where the Notification Services data objects are installed. The NSHost parameter describes the server where the Notification Services instance is installed. The BaseDirectoryPath describes the folder that contains the folders and files that are referenced in the metadata. The actual declaration is shown in the XML code below:

<Parameters>
  <Parameter>
    <Name>_DBSystem_</Name> 
    <Value>%SqlServer%</Value> 
  </Parameter>
  <Parameter>
    <Name>_BaseDirectoryPath_</Name> 
    <Value>%BaseDirectoryPath%</Value> 
  </Parameter>
  <Parameter>
    <Name>_NSSystem_</Name> 
    <Value>%NSHost%</Value> 
  </Parameter>
</Parameters>

The parameters declared in the configuration file are available in the ADF as well. This next example is taken from a part of the ADF that describes the location of the generator component. Notice that the parameter is referenced using the % sign before and after the name of the parameter.

<Generator>
  <SystemName>%_NSSystem_%</SystemName> 
</Generator>

Finally, the parameters that are defined in the configuration file and used throughout the metadata are set to the dynamic values using the NsControl.exe command-line program. This program is used, among other functions, to create the Notification Services instance. Below is an example of the NsControl.exe create command, including the three properties shown above:

NsControl create -in config.xml SqlServer=MyServer NSHost=MyServer 
BaseDirectoryPath="C:\Program Files\Microsoft\SmallLibrary\Source"
Note   It is possible to set default values for parameters.

Compiling the ADF and Configuration Files

Once the design process is complete, use the NsControl.exe command-line program to create, register, and enable the Notification Services instance. The SmallLibrary sample includes instructions in SetupGuide.txt that will guide you through the manual installation process. If you like, you may also choose to automate the process of compiling the project using the NsControl.exe command-line program. To do this, you'll need to design a script and set command-line parameters that are used when you build the project. If you choose this technique, make sure that the service user account access credentials (user name and password) are recorded in a secure location.

The Notification Services API

Microsoft Notification Services installs a managed code API (the namespace is Microsoft.SqlServer.NotificationServices). The objects and methods of this class library represent the data access layer for the Notification Services data sources. Including the class library equips Web applications with subscriber management, subscription creation, and the ability to commit events to the Notification Services data sources.

Note   Microsoft cautions developers about the potential maintenance consequences of writing code that circumvents the Notification Services class library in an effort to use the objects in the Notification Services data sources directly. Most of the objects in the data sources are designed to be used exclusively by the service instance and the class library. The naming convention, content, and structure of these data source objects may change in future releases of the product. The few data source objects that are available for this kind of use are identified in the Notification Services documentation. See the help topics, "Notification Services Views" and "Stored Procedure Reference."

In the SmallLibrary sample, the code that manages subscribers, creates subscriptions, and commits events is located in the BusinessLogic.vb module. This is found in the NotificationLogic project.

Subscriber Management

The Notification Services class library is used to manage subscribers. Subscribers identify their language, time zone, devices, and the addresses of those devices. With respect to the subscriber's devices, the name of the delivery channel that is defined in the configuration file is associated with the devices that the user would like to use to receive content from that delivery channel.

In the SmallLibrary sample, there are two places where subscriber management is implemented. When a new patron account is created in the SmallLibrary database, a subscriber is also created. The other subscriber maintenance action that is implemented is an update. When the patron records a change of e-mail address in the SmallLibrary database, the address of the corresponding device must also be changed.

Creating a Subscriber

In this first code sample, the InsertSubscriber subroutine adds the specified patron to the Notification Services data sources. Notice that this code does not add any attributes of the subscriber. Enabled and SubscriberId are the only properties of the Subscriber object. Other attributes of a subscriber, such as the subscriber's devices, are intentionally decoupled from the Subscriber object. Even though the SmallLibrary records only one e-mail address per patron, it is easy to see how recording the device information (and other variable subscriber information) separately more closely represents the reality that a subscriber might have more than one e-mail address or device capable of receiving a message.

Sub InsertSubcriber(ByVal iPatronId As Integer)
  Dim oInstance As New NSInstance("SmallLibraryInstance")
  Dim oSubscriber As New Subscriber(oInstance)
  oSubscriber.Enabled = True
  oSubscriber.SubscriberId = CStr(iPatronId)
  oSubscriber.Add()
End Sub

Once the patron's subscriber account is added, at least one device is related to the patron’s subscriber account using the SubscriberDevice object. The DeviceName property, combined with the SubscriberId, uniquely identifies a device in the system. In this example, since patrons only record one e-mail address in the SmallLibrary database, the DeviceName property is set to "Email" for all subscriber devices that are recorded.

Sub InsertSubscriberEmail( _
  ByVal iPatronId As Integer, _
  ByVal sEmail As String)
  Dim oInstance As New NSInstance("SmallLibraryInstance")
  Dim oDevice As New SubscriberDevice(oInstance)
  oDevice.SubscriberId = CStr(iPatronId)
  oDevice.DeviceAddress = sEmail
  oDevice.DeliveryChannelName = "EmailChannel"
  oDevice.DeviceTypeName = "Email"
  oDevice.DeviceName = "Email"
  oDevice.Add()
End Sub

Updating a Device Address

All that is needed to update a device address is the SubscriberId, the name of the device, and the device address. As explained above, for the limitations of the SmallLibrary system, there is only one device per patron. So the device name is "Email" for all device records in this sample. The procedure below updates an e-mail address for a subscriber.

Sub UpdateSubscriberEmail( _
  ByVal iPatronId As Integer, _
  ByVal sEmail As String)
  Dim oInstance As New NSInstance("SmallLibraryInstance")
  Dim oDevice As New SubscriberDevice(oInstance)
  oDevice.SubscriberId = CStr(iPatronId)
  oDevice.DeviceName = "Email"
  oDevice.DeviceAddress = sEmail
  oDevice.DeliveryChannelName = "EmailChannel"
  oDevice.DeviceTypeName = "Email"
  oDevice.Update()
End Sub

Creating a Subscription

The Notification Services class library is used to create subscriptions for subscribers. Fields of the Subscription object map to the fields in the subscription class schema.

In the SmallLibrary sample, the HoldSubscription subscription class has three fields defined: "DeviceName", "SubscriberLocale", and "TitleId".

Public Function InsertHoldNoticeSubscription( _
  ByVal iPatronId As Integer, _
  ByVal iTitleId As Integer) As String
  Dim sSubscriptionId As String
  Dim oInstance As New NSInstance("SmallLibraryInstance")
  Dim oApplication As New NSApplication( _
    oInstance, "SmallLibrary")
  Dim oSubscription As New Subscription( _
    oApplication, "HoldSubscription")
  oSubscription.Enabled = True
  oSubscription.SubscriberId = CStr(iPatronId)
  oSubscription("DeviceName") = "Email"
  oSubscription("SubscriberLocale") = "en-US"
  oSubscription("TitleId") = iTitleId
  sSubscriptionId = oSubscription.Add()
  Return sSubscriptionId
End Function

Once this subscription is enabled and then added, the subscriber may eventually be delivered a hold-notice notification.

Committing an Event

The Notification Services class library is used to commit events to the Notification Services data sources. Fields of the Event object map to fields in the event class of the same name. (The event class is defined in the ADF, one of the Notification Services metadata files discussed above.)

In the SmallLibrary sample, when a patron checks in a book, the application commits an event to the Notification Services data sources. Of course, it only does this if there is another patron that has been waiting for a copy of the book to become available.

oInstance = New NSInstance("SmallLibraryInstance")
oApplication = New NSApplication(oInstance, "SmallLibrary")
oEvent = New _
    Microsoft.SqlServer.NotificationServices.Event( _
        oApplication, "HoldEvent")
oEventCollector = New EventCollector( _
    oApplication, "HoldEventProvider")
oEvent("SubscriberId") = iPatronId 
oEvent("HoldId") = iHoldId
oEvent("TitleId") = iTitleId
oEvent("Title") = sTitle
oEventCollector.Write(oEvent)
iCountOfCommittedEvents = oEventCollector.Commit()

Testing Notification Services

Unit testing a Notification Services application is the most complicated part of the development process. Some errors are easily discovered once the NsControl create or NsControl update command is issued. Other errors are not discovered until the first notification is generated or should have been generated. This section describes the preparation work that will need to take place for testing. It also discusses some common debugging steps to follow in order to help identify bugs in the Notification Services metadata files.

Preparation Work for Testing

There are a few important matters to take care of before you begin testing. To begin, turn off your SMTP service. You will discover success or failure of your Notification Service without actually delivering the test messages through the wire. Messages will accumulate in the Pickup directory. The default location of the Pickup directory is: C:\Inetpub\mailroot\Pickup.

Your Web application may not have permission to manage subscribers or create subscriptions. The account that the Web application uses should exist in the Microsoft SQL Server database where the Notification services data sources are installed. In both of the Notification Services databases, grant the account access privileges to the minimum set of roles that are needed to permit the Web application to manage subscribers or create subscriptions. For the SmallLibrary sample, the default user account is ASPNET and the roles that the account needs to be mapped to are NSReader, NSSubscriberAdmin, NSEventProvider, and NSAdmin.

Note   Granting administrative privileges of any kind to ASPNET is a security risk that should be avoided. Instead, create a specific Windows user account for the Web application and a SQL Server login for that account. Restrict access for the login to the minimum set of roles needed for the Web application function.

Debugging the Metadata Files

As mentioned above, Microsoft Notification Services is a feature-rich product designed to interact with many systems. However, there are a number of problems that can prevent the files from compiling. The most common of these include:

  1. Transact-SQL syntax is not valid because of an XML-reserved character. You will need to escape > or < with &gt; and &lt;.
  2. Transact-SQL code excludes objects that are required, or objects that do not exist. When building your first Notification Services application, it is difficult to identify the names of the objects or the set that is required in a selection. Some of the objects, such as events, cannot be seen in the database because they are created when the event occurs.
  3. Some elements must be included even if they do not contain any sub-elements. NsControl.exe output may report that a node is expected when it is actually optional.
  4. In almost all cases, the ordering of the elements must be maintained. For the correct ordering of the ADF file, see the topic, "Application Definition File Reference" in Microsoft Notification Services Books Online.

Once the XML metadata compiles, you may need to grant access to the application that uses or administrates the notification service. Errors that accumulate in the Application Event Log indicate that there is still a problem with the metadata. Most likely, these errors will be Transact-SQL code that was not validated when the metadata was compiled. It is unnecessary to re-create the instance each time the implementation is tested. Using the NsControl.exe program, follow the sequence of disable, update, and enable. Generally, it is not a problem to leave service started while the update takes place. The parameters to the NsControl.exe update command are similar to those of the NsControl.exe create command. Both reference the name of the configuration file and require the set of parameters that may be described in the configuration file.

Other Notification Services Resources

Mark Brown's article, "SQL Server: Build Apps that Provide Real-time Information and Customized Content Using SQL Server Notification Services" in the November 2002 edition of the MSDN Magazine describes how Notification Services has overcome the performance, scalability, and protocol standards issues of the past. He gives an excellent and extensive overview of the Notification Services components, and finishes with a discussion on the deployment and scalability features that are built into the system.

For a more technical overview of Notification Services, download the 23-page white paper from http://www.microsoft.com/sql/techinfo/development/2000/sqlnsto.asp. This document includes discussions on security performance, including scalability and reliability. It discusses how Notification Services as a product compliments similar Microsoft software, such as .NET Alerts and Message Queuing.

Specific technical information for Notification Services, including syntax, use cases, administrative, programming, and maintenance guidance, is included with the product's Notification Services Documentation. The Books Online resource is installed in the Start Menu under "Microsoft SQL Server Notification Services." When you download Microsoft SQL Server Notification Services, use the link, http://www.microsoft.com/sql/ns/download.asp.

Conclusion

Notification Services is a highly configurable notification system that can be incorporated into existing systems with minimal changes to the existing code base. Most of the development work required to incorporate Notification Services as a feature will involve the design and testing of the Notification Services metadata files. These are expressed in XML and processed using the NsControl command-line program.

Two forms of notifications are demonstrated by the SmallLibrary sample. The event-based "Hold Notice" notification is created in response to an event that is created by user activity. The "Overdue Notice" is a schedule-based notification issued periodically. Both of these notifications are delivered to users through an e-mail delivery channel.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft