Flight Sample

The Flight sample is an event-driven Microsoft SQL Server 2005 Notification Services application that produces notifications about flight prices for subscribed users according to their subscriptions.

Scenario

Travel customers subscribe to flight price notifications. In each subscription, they indicate a departure and arrival city, a target price, and optionally an airline.

The company running the Flight application submits flight price event data to the application by dropping XML data into a watched folder. Notification Services then picks up this data and generates notifications for subscribers with matching subscriptions.

Notifications are generated when the following conditions are true:

• The event ticket price is less than the subscription ticket price.
• The event departure airport equals the subscription departure airport.
• The event arrival airport equals the subscription arrival airport.
• The event airline equals the subscription airline (if specified).

If one subscriber has multiple notifications to be sent at the same time, Notification Services uses digest delivery to combine the multiple notifications. Notification Services then formats the notification using an XSLT content formatter and uses the specified delivery channel (e-mail or file) to distribute the notification.

Languages

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

Features

The Flight sample uses the following features of Notification Services:

Prerequisites

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

• 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.
  • .NET Framework SDK 2.0 or Microsoft Visual Studio 2005. You can obtain .NET Framework SDK free of charge. See Installing the .NET Framework SDK.

Also make sure that the following Microsoft Windows components are installed and operational:

• Simple Mail Transfer Protocol (SMTP) Service, which is a component of Internet Information Services (IIS). On Microsoft Windows Server 2003, SMTP is not installed by default with IIS. See your Windows documentation for instructions on how to install and use the SMTP Service.

Building the Notification Services Instance

To build the instance, you will have to do the following:

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

The following procedures show how to do each of these tasks for the Flight 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 select New Notification Services Instance.

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

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

   • SampleDirectory: the path of the Flight sample's root folder. The default path is C:\Program Files\Microsoft SQL Server\90\Samples\Notification Services\Flight.
   • NotificationServicesHost: the name of the Notification Services server
   • SQLServer: 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 FlightInstance, 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 logon, enter a Windows logon 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.

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

1. In Object Explorer, expand the Security node.

2. If you must 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 FlightInstanceNSMain database:

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

6. Grant permissions for the FlightInstanceFlight database:

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

7. Click OK to apply SQL Server permissions.

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

   1. Locate the 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. Locate the 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.

Generating a Strong Name Key File

If you have not already created a strong name key file, generate the key file using the following instructions.

To generate a strong name key file

1. Open a Microsoft Visual Studio 2005 command prompt. Click Start, point to All Programs, point to Microsoft .NET Framework SDK 2.0, and then click SDK Command Prompt.

   -- or --

   Open a Microsoft .NET Framework command prompt. Click Start, point to All Programs, point to Microsoft .NET Framework SDK 2.0, and then click SDK Command Prompt.

2. Use the change directory command (CD) to change the current directory of the command prompt window to the folder where the samples are installed.

   Note

   To determine the folder where samples are located, click the Start button, point to All Programs, point to Microsoft SQL Server, point to Documentation and Tutorials, and then click Samples Directory. If the default installation location was used, the samples are located in <system_drive>:\Program Files\Microsoft SQL Server\100\Samples.

3. At the command prompt, run the following command to generate the key file:

   sn -k SampleKey.snk

   Important

   For more information about the strong-name key pair, see "Security Briefs: Strong Names and Security in the .NET Framework" in the .NET Development Center on MSDN.

Running the Sample

To run the sample, you must

• Start the instance.
• Add subscribers and subscriptions.
• Drop an XML file that contains events.
• View the resulting notifications.

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 FlightInstance and then select Start.

Step 2: Add subscribers and subscriptions

1. Build the Flight or Flight_VB Visual Studio Solution

   If you are using the Microsoft .NET Framework SDK, do the following:

   1. Click Start, point to All Programs\Microsoft .NET Framework SDK v2.0, and then click SDK Command Prompt.
   2. Locate the Flight sample's root folder. Type the following command for the default location:
      cd \Program Files\Microsoft SQL Server\90\Samples\Notification Services\Flight
   3. Type the following to build the Visual Studio solution.
      [C#] 
      msbuild Flight.sln
      [Visual Basic] 
      msbuild Flight_VB.sln

   Or, if you are using Visual Studio 2005, do the following:

   1. Open the solution file of your choice (Flight.sln or Flight_VB.sln).
   2. Build the solution.

2. Run AddSubscribers.exe.

   The default location for this file is C:\Program Files\Microsoft SQL Server\90\Samples\Notification Services\Flight\AddSubscribers\language\AddSubscribers\bin\Debug.

3. Run AddSubscriptions.exe.

   C:\Program Files\Microsoft SQL Server\90\Samples\Notification Services\Flight\AddSubscriptions\language\AddSubscriptions\bin\Debug.

Step 3: Submit events

1. In Windows Explorer, locate the Flight 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 name extension to .done.

   If there is an error when reading or submitting data, the event provider will change the file name 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, locate the Flight sample's Notifications folder. You should find a file named FileNotifications.txt. This file contains the file-based 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 steps to remove the Flight sample.

To remove the Flight sample

1. In SQL Server Management Studio Object Explorer, open the Notification Services folder.

2. Right-click FlightInstance and then select Stop.

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

4. Right-click FlightInstance, 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