Lesson 10: Adding Subscribers and Subscriptions

Lesson 10: Adding Subscribers and S...

Switch on low bandwidth view

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

Language Filter

SQL Server 2005 Books Online (November 2008)

Lesson 10: Adding Subscribers and Subscriptions

In this lesson, you will review two VBScript files to understand how they submit subscriber and subscription data to Notification Services, and then you will run the scripts to add subscriber data to the instance database and subscription data to the application database.

Subscriber and Subscription Management

Notification Services generates notifications based on subscriptions created by subscribers. These subscribers can be people or other applications. As part of developing a Notification Services application, you develop one or more interfaces so that subscribers can manage their subscriptions.

Subscriber data includes a subscriber ID and information about the subscriber's devices, such as e-mail addresses and text messaging addresses. Each subscriber can have multiple devices.

Subscriber data is stored in instance database objects. All applications hosted by an instance of Notification Services use the same subscriber data.

Subscription data specifies what application information the subscriber is interested in, such as the city for weather forecasts, and which device receives the notifications. Your application might have built-in logic to select subscriber devices based on conditional logic, such as day, time, or message importance.

Subscription data is stored in application databases. Each application has its own subscription data.

Most Notification Services applications provide an interface that subscribers can use to manage their subscriber and subscription data. This is frequently an ASP.NET application, but can be a Windows application, Web service, or any other interface you want to develop using the Notification Services subscription management objects. For more information, see Developing Subscription Management Interfaces.

Review the Weather Subscription Management Interface

For simplicity, the Weather application bulk loads its subscriber and subscription data using two VBScript files. This is not a common way to manage subscribers and subscriptions, but is useful for prototyping and testing your notification applications.

These scripts illustrate how you can call Notification Services interfaces from COM components. Notification Services is written in managed code (C#) and is designed to be accessible from both managed and unmanaged code.

AddSubscribers.vbs

The first VBScript, called AddSubscribers.vbs, adds three subscribers to the Weather instance.

First, create NSInstance and Subscriber objects:

Dim nsInstance, nsSubscriber, nsSubscriberDevice
' Create and initialize NSInstance object.
Set nsInstance = WScript.CreateObject("Microsoft.SqlServer.NotificationServices.NSInstance")
nsInstance.Initialize "Tutorial"
' Create and initialize NSSubscriber object.
Set nsSubscriber = WScript.CreateObject("Microsoft.SqlServer.NotificationServices.Subscriber")
nsSubscriber.Initialize (nsInstance)

Next, add three subscribers using the Subscriber object:

' Add subscribers.
nsSubscriber.SubscriberId = "stephanie"
nsSubscriber.Add

nsSubscriber.SubscriberId = "david"
nsSubscriber.Add

nsSubscriber.SubscriberId = "richard"
nsSubscriber.Add

Finally, create a SubscriberDevice object and add subscriber devices for each subscriber:

' Create NSSubscriberDevice object.
Set nsSubscriberDevice = WScript.CreateObject("Microsoft.SqlServer.NotificationServices.SubscriberDevice")
nsSubscriberDevice.Initialize (nsInstance)

' DeviceName must match subscriptions that use this device
nsSubscriberDevice.DeviceName = "myDevice"

' Add a file device for each subscriber
nsSubscriberDevice.SubscriberId = "stephanie"
nsSubscriberDevice.DeviceTypeName = "File"
nsSubscriberDevice.DeviceAddress = "stephanie@adventure-works.com"
nsSubscriberDevice.DeliveryChannelName = "FileChannel"
nsSubscriberDevice.Add

nsSubscriberDevice.SubscriberId = "david"
nsSubscriberDevice.DeviceTypeName = "File"
nsSubscriberDevice.DeviceAddress = "david@adventure-works.com"
nsSubscriberDevice.DeliveryChannelName = "FileChannel"
nsSubscriberDevice.Add

nsSubscriberDevice.SubscriberId = "richard"
nsSubscriberDevice.DeviceTypeName = "File"
nsSubscriberDevice.DeviceAddress = "richard@adventure-works.com"
nsSubscriberDevice.DeliveryChannelName = "FileChannel"
nsSubscriberDevice.Add

AddSubscriptions.vbs

The other VBScript, called AddSubscriptions.vbs, adds one subscription for each of our three subscribers.

First, create and initialize the NSInstance, NSApplication, and Subscription objects:

Dim nsInstance, nsApplication, nsSubscription
' Create NSInstance object.
Set nsInstance = WScript.CreateObject("Microsoft.SqlServer.NotificationServices.NSInstance")
nsInstance.Initialize "Tutorial"

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

' Create Subscription object.
Set nsSubscription = WScript.CreateObject("Microsoft.SqlServer.NotificationServices.Subscription")
nsSubscription.Initialize (nsApplication), "WeatherCity"

Next, set the common properties for all subscriptions you are loading:

nsSubscription.SetFieldValue "DeviceName", "myDevice"
nsSubscription.SetFieldValue "SubscriberLocale", "en-us"

Finally, add the subscriptions:

nsSubscription.SubscriberId = "stephanie"
nsSubscription.SetFieldValue "City", "Seattle"
nsSubscription.Add

nsSubscription.SubscriberId = "david"
nsSubscription.SetFieldValue "City", "Orlando"
nsSubscription.Add

nsSubscription.SubscriberId = "richard"
nsSubscription.SetFieldValue "City", "Seattle"
nsSubscription.Add

Load Subscriber and Subscription Data

Now that you are familiar with the scripts, use them to load subscriber data into the Tutorial instance and subscription data into the Weather application.

To load subscriber and subscription data

Using Windows Explorer, locate the tutorial Weather folder. The default location for this folder is C:\Program Files\Microsoft SQL Server\90\Samples\Notification Services\tutorial\Weather.
Double-click AddSubscribers.vbs.

You should receive the message, "Subscribers successfully added."
Double-click AddSubscriptions.vbs.

You should receive the message, "Subscriptions successfully added."

Note:
If you install Notification Services 2.0 after you install SQL Server 2005 Notification Services, the wrong versions of the assemblies will be registered for COM interop and these scripts will not work. For more information, see How to: Register the Core Notification Services Assembly for COM Interop.

View Subscriber Data

After you add subscriber data to the application, you can view the subscriber and device information using the ViewSubscribersAndDevices.sql query.

To view subscriber data

In Solution Explorer, expand Weather, expand Queries, and then double-click ViewSubscribersAndDevices.sql.
Press F5 to run the query.

You should see subscriber and device information for three subscribers.
Close ViewSubscribersAndDevices.sql.

View Subscription Data

After you add subscription data to the application, you can view the subscriptions information using the ViewSubscriptions.sql query.

To view subscription data

In Solution Explorer, double-click ViewSubscriptions.sql.
Press F5 to run the query.

You should see three subscriptions, one for each subscriber.
Close ViewSubscriptions.sql.

Next Lesson

Lesson 11: Submitting Events to the Weather Application

AddSubscribers.vbs

AddSubscriptions.vbs

Concepts

Other Resources

Help and Information