Export (0) Print
Expand All

FlightNmo Sample

SQL Server 2005

Updated: 17 July 2006

The FlightNmo sample shows how to build the Flight sample application using the Notification Services Management Objects (NMO).

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 FlightNmo 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.

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

The FlightNmo sample uses the following features of Notification Services :

Application Area Features

Event Class

Basic event queue (no chronicle).

Subscription Class

Event-driven subscriptions.

Notification Class

Digest delivery.

Event Providers

File system watcher event provider.

Content Formatters

XSLT content formatter.

Delivery Protocols

File and SMTP delivery protocols.

Instance and Application Development

NMO

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

  • SQL Server 2005, including the following components:
    • Database Engine.
    • Notification Services engine and client components.
    • 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.

Make sure that the following 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.

Also make sure that the instance of the Database Engine you will use is started.

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

  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.
    ms160822.note(en-US,SQL.90).gifNote:
    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

    ms160822.note(en-US,SQL.90).gifImportant:
    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.

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

  • Create and 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 FlightNmo sample.

  1. First, build the 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. Find the FlightNmo sample's root folder. Type the following command for the default location:
      cd \Program Files\Microsoft SQL Server\90\Samples\Notification Services\FlightNmo
    3. Type the following to build the solution:
      [C#] 
      msbuild FlightNmo.sln
      [Visual Basic] 
      msbuild FlightNmo_VB.sln

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

    1. Open the solution file of your choice (FlightNmo.sln or FlightNmo_VB.sln).
    2. From the Visual Studio Build menu, select Build Solution.

  1. At the command prompt, go to the CreateFlightNmoInstance Debug folder.

    cd CreateFlightNmoInstance\ language \CreateFlightNmoInstance\bin\Debug

  2. Run CreateFlightNmoInstance.exe as shown below to create, register, and enable the instance. The SqlUserName and SqlPassword arguments are required only if using SQL Server Authentication. All other arguments are required:

    CreateFlightNmoInstance SqlServer= databaseServer SampleDirectory=directory

    NotificationServicesHost= NotificationServicesHost

    ServiceUserName= NSServiceUserName ServicePassword=NSServicePwd

    SqlUserName= NSServiceSqlUserName SqlPassword=NSServiceSqlPwd

    When you are running this command, use the following values for the placeholders that were mentioned earlier in this topic:

    • databaseServer is the Database Engine instance on which to create the instance and application databases.
    • directory is the sample directory. The default location is C:\Program Files\Microsoft SQL Server\90\Samples\Notification Services\FlightNmo.
    • NotificationServicesHost is the server that runs the Notification Services engine components. For this sample, you should use the local computer.
    • NSServiceUserName is the account the NS$FlightNmoInstance Windows Service will run under.
    • NSServicePwd is the password for the NSServiceUserName account.
    • Use SQL Server Authentication arguments, NSServiceSqlUserName and NSServiceSqlPwd, only if you are using SQL Server Authentication. If these arguments are provided, the Windows service will use them to connect to the Database Engine.

    When this command is completed, you will have two new databases and a Windows service. The databases are named FlightNmoInstanceNSMain and FlightNmoInstanceFlightNmo. The Windows service is named NS$FlightNmoInstance.

ms160822.note(en-US,SQL.90).gifNote:
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.

  1. Open SQL Server Management Studio and connect to the instance of the Database Engine you specified in the SqlServer argument earlier in this topic.

  2. In Object Explorer, expand the Security node.

  3. 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 in the ServiceUserName argument earlier in this topic.
    • To use SQL Server Authentication, select SQL Server authentication and then enter the values you specified for the SqlUserName and SqlPassword arguments earlier in this topic.
  4. If the login used by the Windows service already has access to SQL Server, right-click the login and select Properties.

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

  6. Grant permissions for the FlightNmoInstanceNSMain database:

    1. In the Users mapped to this login box, select FlightNmoInstanceNSMain.
    2. In the Database role membership for: FlightNmoInstanceNSMain box, select NSRunService.
  7. Grant permissions for the FlightNmoInstanceFlightNmo database:

    1. In the Users mapped to this login box, select FlightNmoInstanceFlightNmo.
    2. In the Database role membership for: FlightNmoInstanceFlightNmo box, select NSRunService.
  8. Click OK to apply SQL Server permissions.

  9. 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.
  10. 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.

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 perform these tasks.

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

  2. Right-click FlightNmoInstance and then select Start.

  1. Run AddSubscribers.exe.

    The default location of this folder is C:\Program Files\Microsoft SQL Server\90\Samples\Notification Services\FlightNmo\AddSubscribers\language\AddSubscribers\bin\Debug.

  2. Run AddSubscriptions.exe.

    The default location of this folder is C:\Program Files\Microsoft SQL Server\90\Samples\Notification Services\FlightNmo\AddSubscriptions\language\AddSubscriptions\bin\Debug.

ms160822.note(en-US,SQL.90).gifNote:
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.

  1. In Windows Explorer, locate the FlightNmo 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.

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

  2. Using Windows Explorer, locate the FlightNmo 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.

Follow these steps to remove the FlightNmo sample.

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

  2. Right-click FlightNmoInstance and then select Stop.

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

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

Release History

14 April 2006

Added content:
  • Added note about running samples using database owner permissions.

5 December 2005

Changed content:
  • Changed instructions for generating a key file, including the name and location of the key file.

Community Additions

ADD
Show:
© 2014 Microsoft