Share via

Newsletter Sample

  • Article

The Newsletter sample shows how to create a simple Web-based subscription management application. The sample has an n-tier design containing an HTML-based interface layer and a subscription management middle-tier layer. A Microsoft Visual Studio Web project creates a Visual Web Developer Web Server when you run the Web project so that you can more easily use the sample. This sample is not supported on IA64.

Scenario

Adventure Works Cycles publishes customer-oriented newsletters and sponsors promotional events. Using this application, customers can subscribe to receive these newsletters and notifications about promotional events.

A customer uses a Web page to create a subscription for a type of information (newsletter or promotion) and selects when it should be delivered (event-driven or scheduled).

At Adventure Works Cycles, a file containing newsletter data and promotional event data is dropped into a watched folder. When the generator runs, it runs event chronicle rules to update the chronicle used for scheduled subscriptions, then runs the event-driven rules against unprocessed event batches, and then runs the scheduled subscription rules for all scheduled subscriptions due to be processed. When the scheduled subscription rule runs, it updates a subscription chronicle used to track when each subscription last produced a notification. This information is used to ensure against duplicate notifications.

Notifications are generated under the following conditions:

  • The document type of the event (newsletter or promotion) matches the document type in the subscription.
  • The category of the newsletter or event matches the category in the subscription.
  • If the subscription is a scheduled subscription, the scheduled time occurred in the interval between the last generator rule firing and the current generator rule firing.
  • If the subscription is a scheduled subscription, the time the event was committed to the database is greater than the time the subscription last generated a notification.

The resulting notifications are formatted using an XSLT content formatter and then sent using the File delivery protocol.

Languages

XML, XSD, XSLT, and Microsoft Visual C# or Microsoft Visual Basic.

Features

The Newsletter sample uses the following features of Microsoft SQL Server Notification Services.

Application Area Features

Event Class

A basic event class for event-driven subscriptions and an event chronicle for scheduled subscriptions.

Subscription Class

Event-driven and scheduled subscriptions.

Notification Class

Digest delivery.

Event Providers

File system watcher event provider.

Content Formatters

XSLT content formatter.

Delivery Protocols

File and SMTP delivery protocols.

Subscription Management Application

Microsoft ASP.NET using Visual Web Developer Web Server.

Prerequisites

Before running this sample, make sure the following software is installed:

  • Microsoft SQL Server 2005, including the following components:
    • Database Engine.
    • Notification Services.
    • SQL Server Management Studio.
    • Notification Services samples. These samples are included with SQL Server 2005. You can download the latest version of the samples at the SQL Server Developer Web site.
  • Microsoft Visual Studio 2005, including Visual Web Designer

Building the Sample

To build the sample, you will need to do the following:

  • Create the instance of Notification Services.
  • Register the instance of Notification Services.
  • Grant SQL Server and database permissions.

The following procedures show how to do each of these tasks for the Newsletter sample.

Step 1: Create the instance of Notification Services

  1. Open SQL Server Management Studio and connect to an instance of SQL Server.

  2. In Object Explorer, right-click the Notification Services folder, and then select New Notification Services Instance.

  3. In the New Notification Services Instance dialog box, click Browse and select the InstanceConfig.xml file in the Newsletter sample's root folder.

  4. In the Parameters box, enter values for the three parameters:

    • SampleDirectory is the path of the Newsletter sample's root folder. The default path is C:\Program Files\Microsoft SQL Server\90\Samples\Notification Services\Newsletter.
    • NotificationServicesHost is the local server name.
    • SQLServer is the name of the SQL Server instance.

  5. Select the Enable instance after it is created check box.

  6. Click OK.

  7. When Notification Services has finished creating the instance, click Close.

Step 2: Register the instance of Notification Services

  1. In SQL Server Management Studio, open the Notification Services node, right-click NewsletterInstance, point to Tasks, and then select Register.

  2. In the Register dialog box, select the Create Windows service check box. The Windows service runs the instance of Notification Services on this computer.

  3. Under Service login, enter a Windows account and password. This is the Windows account under which the service will run. If you use Windows Authentication to access SQL Server, the Windows service will also use this account to connect to SQL Server.

  4. If you must use SQL Server Authentication to access SQL Server, select SQL Server Authentication under Authentication, and then enter a SQL Server login name and password.

    We recommend using Windows Authentication.

  5. Click OK.

  6. When Notification Services has finished registering the instance, click Close.

Note

If you are deploying a Notification Services sample using an account that is the database owner or a system administrator, you do not have to grant SQL Server permissions to the account. Granting these permissions might result in an error, which you can ignore. When you deploy your own applications, you should use accounts with lower privileges to help improve security.

Step 3: Grant SQL Server, database, and folder permissions

  1. In Object Explorer, expand the Security node.

  2. If you need to create a new database login account for the Windows service, right-click Logins and select New Login and create the Login account:

    • To use Windows Authentication, select Windows authentication, and enter the same Windows account you specified when registering the instance.
    • If you must use SQL Server Authentication, select SQL Server authentication and then enter the same SQL Server login and password you specified when registering the instance.

  3. If the login used by the Windows service already has access to SQL Server, right-click the login and select Properties.

  4. In the left pane of the Login dialog box, select User Mapping.

  5. Grant permissions for the NewsletterInstanceNSMain database:

    1. In the Users mapped to this login box, select NewsletterInstanceNSMain.
    2. In the Database role membership for: NewsletterInstanceNSMain box, select NSRunService.

  6. Grant permissions for the NewsletterInstanceNewsletter database:

    1. In the Users mapped to this login box, select NewsletterInstanceNewsletter.
    2. In the Database role membership for: NewsletterInstanceNewsletter box, select NSRunService.

  7. Click OK to apply SQL Server permissions.

  8. Using Windows Explorer, configure security for the Events folder:

    1. Navigate to the Newsletter sample's Events folder.
    2. Right-click the Events folder, select Sharing and Security, and then select the Security tab.
    3. Click Add and add the account used by the Windows service.
    4. In the Group or user names box, select the account you just added.
    5. In the Permissions for box, select the Read and Modify permissions.
    6. Click OK to apply the changes.

  9. Configure security for the Notifications folder:

    1. Navigate to the Newsletter sample's Notifications folder.
    2. Right-click the Notifications folder, select Sharing and Security, and then select the Security tab.
    3. Click Add and add the account used by the Windows service.
    4. In the Group or user names box, select the account you just added.
    5. In the Permissions for box, select Write.
    6. Click OK to apply the changes.

Running the Sample

To run the sample, you need to do the following:

  • Start the instance.
  • Use a Web application to add subscribers and subscriptions.
  • Drop an XML file containing events.

The following procedures show how to do these tasks and how to then view the resulting notifications.

Step 1: Start the instance

  1. In Object Explorer, open the Notification Services folder.

  2. Right-click NewsletterInstance and select Start.

Step 2: Add subscribers and subscriptions

  1. Open Visual Studio 2005.

  2. From the File menu, point to Open and then click Web Site.

  3. In the Open Web Site dialog box, navigate to the Newsletter sample's SubscriptionManagementWeb\cs\SubscriptionManagementWeb folder and select Open.

  4. Press the F5 key to run the Web application.

    Use the Web page to add subscriptions for the sample user to the Newsletter application. The default subscription values will result in a notification.

Note

For production applications, or if you are running this application under a restricted account, add the application's SQL Server login to the NSSubscriberAdmin database role in the instance and application databases.

Step 3: Submit events

  1. In Windows Explorer, navigate to the Newsletter sample's root folder, and then copy the EventData.xml file to the Events subfolder.

    When you drop the file, the file system watcher event provider will read the data from the file, submit it to the application, and then change the file extension to .done.

    If there is an error when reading or submitting data, the event provider will change the file extension to .err. Look in the Windows Event Viewer for additional details about the error.

Step 4: View notifications

  1. Give Notification Services approximately one minute to produce notifications.

  2. Using Windows Explorer, navigate to the Newsletter sample's Notifications folder. You should find a file named FileNotifications.txt. This file contains the notifications.

  3. Navigate to the folder where e-mail messages are dropped. This is typically in one of the folders in C:\Inetpub\mailroot. Depending on the SMTP server state, the notifications should be in either the Pickup folder or Queue folder. If the SMTP service is running, the messages may be moved to the Badmail folder.

Removing the Sample

Use the following procedure to remove the Newsletter sample.

To remove the Newsletter sample

  1. In Object Explorer, open the Notification Services folder.

  2. Right-click NewsletterInstance and then select Stop.

  3. Right-click NewsletterInstance, point to Tasks, and then select Unregister.

  4. Right-click NewsletterInstance, point to Tasks, and then select Delete.

See Also

Other Resources

SQL Server Notification Services Samples
Securing Notification Services

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006
Changed content:
  • Added Visual Web Designer as prerequisite for running this sample.

14 April 2006
Changed content:
  • Added note about running samples using database owner permissions.