<?xml version="1.0" encoding="utf-8" ?>
<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">

<Version>
   <Major>1</Major>
   <Minor>0</Minor>
   <Build>1</Build>
   <Revision>0</Revision>
</Version>
<EventClasses>
   <EventClass>
      <EventClassName>;OrdersE</EventClassName>
      <Schema>
         <Field>
            <FieldName>OrderID</FieldName>
            <FieldType>int</FieldType>
            <FieldTypeMods>not null</FieldTypeMods>
         </Field>
         <Field>
            <FieldName>Item</FieldName>
            <FieldType>varchar(100)</FieldType>
         </Field>
         <Field>
            <FieldName>Quantity</FieldName>
            <FieldType>int</FieldType>
         </Field>
         <Field>
            <FieldName>Department</FieldName>
            <FieldType>char(3)</FieldType>
         </Field>
         <Field>
            <FieldName>PaymentStatus</FieldName>
            <FieldType>char(3)</FieldType>
         </Field>
      </Schema>
      <IndexSqlSchema>
         <SqlStatement>CREATE INDEX DepartmentIndex ON OrdersE ( Department )
         </SqlStatement>
         <SqlStatement>CREATE INDEX PaymentStatusIndex ON OrdersE ( PaymentStatus )
         </SqlStatement>
      </IndexSqlSchema>
   </EventClass>
</EventClasses>

<SubscriptionClasses>
   <SubscriptionClass>
      <SubscriptionClassName>OrdersS</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>Department</FieldName>
            <FieldType>char(3)</FieldType>
         </Field>
         <Field>
            <FieldName>PaymentStatus</FieldName>
            <FieldType>char(3)</FieldType>
         </Field>
      </Schema>
      <EventRules>
         <EventRule>
            <RuleName>OrderEntrySubscriptionsEventRule</RuleName>
            <Action>
                           SELECT dbo.OrdersNNotify(
                               s.SubscriberId, s.DeviceName, s.SubscriberLocale,
                               e.OrderID, e.Item, e.Quantity, 
                               e.Department, e.PaymentStatus)
                           FROM OrdersE e INNER JOIN OrdersS s
                           ON e.Department = s.Department
                           WHERE LEFT(e.PaymentStatus, 1) = s.PaymentStatus
            </Action>
            <EventClassName>OrdersE</EventClassName>
         </EventRule>
      </EventRules>
   </SubscriptionClass>
</SubscriptionClasses>

<NotificationClasses>
   <NotificationClass>
      <NotificationClassName>OrdersN</NotificationClassName>
      <Schema>
         <Fields>
         <Field>
            <FieldName>OrderID</FieldName>
            <FieldType>int</FieldType>
         </Field>
         <Field>
            <FieldName>Item</FieldName>
            <FieldType>varchar(100)</FieldType>
         </Field>
         <Field>
            <FieldName>Quantity</FieldName>
            <FieldType>int</FieldType>
         </Field>
         <Field>
            <FieldName>Department</FieldName>
            <FieldType>char(3)</FieldType>
         </Field>
         <Field>
            <FieldName>PaymentStatus</FieldName>
            <FieldType>char(3)</FieldType>
         </Field>
         </Fields>
      </Schema>
      <ContentFormatter>
         <ClassName>XsltFormatter</ClassName>
         <Arguments>
            <Argument>
               <Name>XsltBaseDirectoryPath</Name>
               <Value>%_BaseDirectoryPath_%</Value>
            </Argument>
            <Argument>
               <Name>XsltFileName</Name>
               <Value>OrderEntry.xslt</Value>
            </Argument>
         </Arguments>
      </ContentFormatter>

      <Protocols>
         <Protocol>
            <ProtocolName>File</ProtocolName>
         </Protocol>
         <Protocol>
            <ProtocolName>SMTP</ProtocolName>
            <Fields>
               <Field>
                  <FieldName>Subject</FieldName>
                  <SqlExpression>'Order Entry Notification'</SqlExpression>
               </Field>
               <Field>
                  <FieldName>BodyFormat</FieldName>
                  <SqlExpression>'html'</SqlExpression>
               </Field>
               <Field>
                  <FieldName>From</FieldName>
                  <SqlExpression>'OrderEntryNotificationService@mycompany.com'
                  </SqlExpression>
               </Field>
               <Field>
                  <FieldName>Priority</FieldName>
                  <SqlExpression>'Normal'</SqlExpression>
               </Field>
               <Field>
                  <FieldName>To</FieldName>
                  <SqlExpression>DeviceAddress</SqlExpression>
               </Field>
            </Fields>

               <!-- Custom Protocol execution parameters omitted for now
               <ProtocolExecutionSettings>
               </ProtocolExecutionSettings>
                    -->
         </Protocol>
      </Protocols>

      <ExpirationAge>PT2H</ExpirationAge>
   </NotificationClass>
</NotificationClasses>

<Providers>
   <NonHostedProvider>
      <ProviderName>EventCreator</ProviderName>
   </NonHostedProvider>
</Providers>

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

<Distributors>
   <Distributor>
      <SystemName>%_NSSystem_%</SystemName>
   </Distributor>
</Distributors>

<ApplicationExecutionSettings>
   <QuantumDuration>P0DT0H0M30S</QuantumDuration>
   <ChronicleQuantumLimit>0</ChronicleQuantumLimit>
   <SubscriptionQuantumLimit>0</SubscriptionQuantumLimit>
   <ProcessEventsInOrder>true</ProcessEventsInOrder>
</ApplicationExecutionSettings>

</Application>

Let’s take a close look at this file. It consists of several sections that describe different parts of the Notification Services process. First important section defines event tables. The table names will derive from the OrdersE class name. The actual table names will be NSCurrentOrdersEEventBatches, NSCurrentOrdersEEvents, NSOrdersEEventBatches, and NSOrdersEEvents. The event tables will have some predefined fields plus the fields you define in the XML file. In my example, the event records consist of OrderID, Item, Quantity, Department, and PaymentStatus fields. <FieldType> values must conform to SQL Server data types. <FieldTypeMods> values are not really well defined in the SS notification Services Books Online; seems that one can use some keywords from the CREATE TABLE SQL statement. Remember that the XML just provides information to the NSControl utility to set up the event tables. You can always change the field descriptions after the tables are set up according to your needs.

You can also set up indexes using the <IndexSqlSchema> element.

Next two sections define subscription and notification tables in a similar way.

Before going any further, we need to understand how these tables work. An event, as you already realize, is a record in the event tables. How events are created entirely depends on your system. You have to develop a mechanism that will store the events in these tables based on the business logic of the system. We will discuss it later in more detail.

Once an event is created, the SS Notification Services match the event to subscriptions. Subscriptions, defined in the subscription table, describe which events actually somebody (a subscriber) is interested in.

The subscription table name will be derived from the OrdersS specified as a subscription class name. The actual table names will be NSCurrentOrderSSubscriptions and NSOrderSSubscriptions. The tables will have some predefined fields in addition to the ones defined in the application definition XML file. We will set up actual subscriptions a little later, now we just develop a foundation for holding them.

I want subscribers to receive messages based on the values of two fields in the event tables. The first field is called Department. I want subscribers to receive events related to their department only. The second field is called PaymentStatus. Payment statuses in the Order Entry system consist of “Success”, “In Progress”, “Failure” and different error statuses. The actual values in the event table are coded with one character (such as “S” for “Success”, “P” for “In Progress”, “F” for “Failure”, and “E” for errors) and (possibly) two digits. For example, error codes might be “E10”, “E20”, etc. For example, if a subscriber needs to receive all error messages, the WHERE LEFT(e.PaymentStatus, 1) = s.PaymentStatus clause will get correct records if the PaymentStatus field in the subscription table is set to “E”. If a subscriber needs to receive only messages with the status “S”, the PaymentStatus field in the subscription table needs to be set to “S”.

The rule, defined in the <Action> element of the XML file, creates a notification every time when there is a record in the events table matching a record in the subscription table.

The SQL statement in the rule uses the OrdersNNotify user-defined function, which name is based on the name of the notification class, OrdersN. Actual notification table names, NSOrdersNNotifications and NSOrdersNNotificationBatches1, also derive from the notification class name.

The OrdersNNotify user-defined function assumes that the notification table will have a few predefined fields. Values for some of the fields (SubscriberID, DeviceName, and SubscriberLocale) will come from the subscription table. Notice that the SubscriberID field is not even defined in the XML. It will be created automatically.

I strongly suggest examining your tables after they are created just to get familiar with the structure of those.

Well, to make a long story short… The Order Entry system creates an event, which is a set of entries in the event tables. The SSNS matches the event to subscriptions and create corresponding records in the notification table for each match. You may notice that events and notifications are actually processed in batches, but this does not significantly change the described process.

A great description of the process you can find on the Microsoft site:
[Microsoft SQL Server Notification Services Technical Overview]]

The picture below is borrowed from that article.

An XSLT file formats the output for my notification e-mails. In fact, the one that I used for this example is quite trivial: it does nothing. Surely, if you have time and eager, you may develop something better. I didn't.

Now we are ready to do the actual magic. To extract it from the examples that have come with SSNS, I spent quite a bit of time. That is what I came up with:

NSControl create -in NS_CONFIG_FILE_NAME.xml

Yes! That is what they call "building a NS application", that’s what is hidden under hundreds of lines of command scripts. That is everything you need. (The NS instance config file specifies application definition files and they may specify XSLT files.) No surprise that we use the NSControl utility again.

The actual command that you need for our Order Entry example is the following:

NSControl create -in OrderEntryNSConfig.xml

Note: to delete the instance you may use the following command:

NSControl delete –force -name INSTANCE_NAME –s SERVER_NAME

If you look into your SQL Server tables, you will notice that after running this command you have a few more databases: one for the instance and one for each application. In my simple example I had only one application, so the NSControl utility created two databases for me. The instance database was called OrderEntryNSMain; the application database was called OrderEntryNSApplication. All tables we discussed above belong to the latter database.

Next step. Enable your NS solution (make sure that the Windows service is running:

NSControl enable -name INSTANCE_NAME

or

NSControl enable -name OrderEntry

for this example.

Now you can add subscribers and subscriptions.

There are several approaches to set up your subscriptions/subscribers. You can develop a Web application that will provide a nice user interface and allow changing the subscriptions on the fly or you can write simple VB scripts for this purpose. Regardless of the method you use, you will essentially do the same thing: create and update SQL Server tables. You can even do it directly using SQL Server Enterprise Manager if you like. To help you with setting up subscriptions, Microsoft comes with a COM interface that you can call from your programs.

I attached a few of VB script code snippets to the zip file with all examples. The code uses a few objects, which methods are quite self-explanatory. For example, using the methods you could a) add subscribers specifying unique subscriber IDs (that what the first snippet does); b) specify parameters for subscriber delivery channels such as e-mail addresses; c) specify actual values for the fields in the subscription table for each subscriber; these values will be used by the subscription rule.

Adding subscribers:

Dim nsInstance, nsApplication, nsSubscriber, nsSubscriberDevice

Set nsInstance = WScript.CreateObject("Microsoft.SqlServer.NotificationServices.NSInstance")
nsInstance.Initialize "OrderEntry"
        
Set nsSubscriber = 
    WScript.CreateObject("Microsoft.SqlServer.NotificationServices.Subscriber")
nsSubscriber.Initialize nsInstance

nsSubscriber.SubscriberId = "CompDept_Delivery"
nsSubscriber.Add

nsSubscriber.SubscriberId = "BooksDept_Errors"
nsSubscriber.Add

Adding e-mail subscriptions:

Dim nsInstance, nsApplication, nsSubscriber, nsSubscriberDevice

Set nsInstance = WScript.CreateObject("Microsoft.SqlServer.NotificationServices.NSInstance")
nsInstance.Initialize "OrderEntry"

Set nsApplication = 
    WScript.CreateObject("Microsoft.SqlServer.NotificationServices.NSApplication")
nsApplication.Initialize nsInstance, "NSApplication"

' 
' Add an E-Mail Device for Each Subscriber
'
Set nsSubscriberDevice = 
    WScript.CreateObject("Microsoft.SqlServer.NotificationServices.SubscriberDevice")
nsSubscriberDevice.Initialize nsInstance
nsSubscriberDevice.DeviceTypeName      = "SMTP"
nsSubscriberDevice.DeviceName          = "myEmailDevice"
nsSubscriberDevice.DeliveryChannelName = "EmailChannel"

nsSubscriberDevice.SubscriberId        = "CompDept_Delivery"
nsSubscriberDevice.DeviceAddress       = "ComputersDelivery.TestMailbox@mycompany.com"
nsSubscriberDevice.Add

nsSubscriberDevice.SubscriberId        = "BooksDept_Errors"
nsSubscriberDevice.DeviceAddress       = "BooksAdmin.TestMailbox@mycompany.com"
nsSubscriberDevice.Add

' 
' Add E-mail Subscriptions
'
Set nsSubscription = 
    WScript.CreateObject("Microsoft.SqlServer.NotificationServices.Subscription")
nsSubscription.Initialize nsApplication, "OrdersS"
nsSubscription.SetFieldValue "DeviceName", "myEmailDevice"
nsSubscription.SetFieldValue "SubscriberLocale", "en-us"

nsSubscription.SubscriberId = "CompDept_Delivery"
nsSubscription.SetFieldValue "PaymentStatus", "S"
nsSubscription.SetFieldValue "Department", "CMP"
nsSubscription.Add

nsSubscription.SubscriberId = "BooksDept_Errors"
nsSubscription.SetFieldValue "PaymentStatus", "E"
nsSubscription.SetFieldValue "Department", "BKS"
nsSubscription.Add

After you run the scripts, you'll find your subscription related tables set up. As a result of these subscriptions all event records having “S00” in the PaymentStatus field and “CMP” in the Department field will create notifications that will be e-mailed to the ComputersDelivery.TestMailbox@mycompany.com address. All event records having “Exx” in the PaymentStatus field (where xx – any two digits) and “BKS” in the Department field will result into notifications that will be e-mailed to the BooksAdmin.TestMailbox@mycompany.com address.

And that's it. Your SSNS “application” is ready. To test it you have to drop a few records into the event table. I have also provided a sample SQL stored procedure to do that. Enjoy.

You can download the code examples from
http://andre.thedovgals.com/professional/ArticlesPresentations/SSNSExample.zip

---

May 2006

---

home

© Copyright Andre Dovgal (Kossoroukov), 2006

---