Welcome Sign in
SQL Server Developer Center
Click to Rate and Give Feedback
MSDN
MSDN Library
SQL Server
SQL Server 2005
Samples
 Inventory Tracker Sample
SQL Server 2005 Books Online (September 2007)
Inventory Tracker Sample

Updated: 17 July 2006

The Inventory Tracking sample shows how to use condition actions to allow subscribers to define their own query clauses for their subscriptions.

You want to allow employees at your company to be notified when inventory levels change. Because different individual look for different data, such as low inventory, high inventory, or significant inventory changes, you want to allow people to create their own conditions for their subscriptions.

In this sample, the subscribers Stephanie and David each create a subscription. Stephanie's subscription specifies that she wants to be notified when the final assembly inventory for product "Road-250 Black, 48" is 35 or fewer units. David's subscription specifies that he wants to be notified when the subassembly inventory for product "HL Crankarm" is 250 units or fewer.

Events are added to the system by an update trigger on the AdventureWorks Production.Inventory table. The trigger submits events to the Inventory Tracker application whenever inventory data is updated. At the end of the generator quantum in which the events arrive, the Inventory Tracker application fires the rules that match event data with subscription data and generate notifications.

Notifications are sent via e-mail to the subscribers.

XML, XSLT, Transact-SQL, Microsoft Visual C# or Microsoft Visual Basic

The Inventory Tracker sample uses the following features of Notification Services.

Application Area Features

Event Class

Basic event queue, no chronicles.

Subscription Class

Subscription rules that are event-driven and use condition actions to support flexible subscription logic. No chronicles.

Notification Class

Digest delivery.

Event Providers

Non-hosted event provider, implemented as an update trigger in the AdventureWorks database.

Content Formatters

XSLT content formatter.

Delivery Protocols

File and SMTP delivery protocols.

Notification Services engine

Microsoft Windows service, created when registering the instance.

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.
    • AdventureWorks OLTP sample database. This database is included with SQL Server 2005, and is also available at the SQL Server Developer Web site. For more information, see Running Setup to Install AdventureWorks Sample Databases and Samples.
  • .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.
  • 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 IIS documentation for instructions on how to install and use the SMTP Service.
    Note:
    Before running this sample, verify that your SMTP service is stopped. Stopping the SMTP service keeps the messages on the server instead of allowing the SMTP service to attempt to send them to non-existent subscribers.

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

  • Create a SQL Server Login for evaluating rules.
  • Create the instance of Notification Services.
  • Register the instance of Notification Services.
  • Grant database permissions to the Windows service.
  • Create triggers and grant database permissions to the rule evaluation account.

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

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

    When an application has rules that contain condition actions, the application requires a login for evaluating conditions. Using this separate login allows you to confine the data that subscribers can create rules against, and helps protect sensitive data.

    Before you create the instance of Notification Services, this login must exist on the server and must not exist in the database. This sample uses a SQL Server Authentication login named NSRulesEvaluator. In your own applications, you can use SQL Server authentication or Windows authentication.

    Note:
    When possible, use Windows Authentication.

  2. In Object Explorer, expand the Security node.

  3. Right-click Logins and select New Login.

  4. Select SQL Server authentication.

    This account is not used for connections to the server. It is used by the Notification Services generator within the instance of the Database Engine to evaluate conditions.

  5. In the Login name box, enter NSRulesEvaluator.

    Enter a password for the NSRulesEvaluator login.

  6. In the Database box, select AdventureWorks.

  7. Click OK.

    If a dialog appears stating that the login has not access to the default database, click OK. You will grant database permissions later in this sample.

  1. In Object Explorer, right-click Notification Services and select New Notification Services Instance.

  2. In the New Notification Services Instance dialog box, click Browse, navigate to the InventoryTracker folder, and then select the InstanceConfig.xml file.

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

    • SampleDirectory: the path of the Inventory Tracker sample's root folder. The default path is C:\Program Files\Microsoft SQL Server\90\Samples\Notification Services\InventoryTracker\.
    • SQLServer: the name of the Database Engine instance.
    • NotificationServicesHost: the name of the server running Notification Services. For the samples, this should be the name of the local computer.

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

  5. Click OK.

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

  1. In Object Explorer, expand Notification Services node, right-click InventoryTrackerInstance, 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. To 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.

  1. In Object Explorer, expand Security.

  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 AdventureWorks database:

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

  6. Click OK to apply permissions.

  7. Configure security for the Notifications folder:

    1. Navigate to 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.

  1. From the SQL Server Management Studio File menu, select Open and then select File.

  2. In the File dialog, open SetupInventoryTrackerSample.sql.

    The default location for this file is C:\Program Files\Microsoft SQL Server\90\Samples\Notification Services\InventoryTracker\SQLScripts.

  3. If the Connect to Database Engine dialog appears, enter your connection information and click Connect.

  4. Press the F5 key to run the Transact-SQL statements.

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.
    Note:
    To determine the folder where samples are located, click the Start button, point to All Programs, point to Microsoft SQL Server 2005, 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\90\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.

To run the sample, you need to

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

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

  1. In Object Explorer, expand Notification Services.

  2. Right-click InventoryTrackerInstance and then select Start.

  1. Build the InventoryTracker or InventoryTracker_VB Visual Studio Solution

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

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

    If you are using Visual Studio 2005, do the following:

    1. Open the solution file of your choice (InventoryTracker.sln or InventoryTracker_VB.sln).
    2. Press F6 to build the solution.

  2. Run AddSubscribers.exe.

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

  3. Run AddSubscriptions.exe.

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

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.

  1. From the SQL Server Management Studio File menu, select Open and then select File.

  2. In the File dialog, open UpdateProductInventory.sql.

    The default location for this file is C:\Program Files\Microsoft SQL Server\90\Samples\Notification Services\InventoryTracker\SQLScripts.

    This script adds an update trigger to the AdventureWorks database and then grants database permissions to the NSRulesEvaluator login.

  3. If the Connect to Database Engine dialog appears, enter your connection information and click Connect.

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

  2. Using Windows Explorer, navigate to the InventoryTracker 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.

  1. From the SQL Server Management Studio File menu, select Open and then select File.

  2. In the File dialog, open ResetProductInventory.sql.

    The default location for this file is C:\Program Files\Microsoft SQL Server\90\Samples\Notification Services\InventoryTracker\SQLScripts.

    This script returns the AdventureWorks database to its previous state, which allows you to run the sample again.

  3. If the Connect to Database Engine dialog appears, enter your connection information and click Connect.

Use the following procedure to remove the Inventory Tracker sample.

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

  2. Right-click InventoryTrackerInstance and then select Stop.

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

  4. Right-click InventoryTrackerInstance, 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.
  • Added information about security best practices for rules evaluator accounts.
Tags What's this?: Add a tag
Community Content   What is Community Content?
Add new content RSS  Annotations
© 2008 Microsoft Corporation. All rights reserved. Terms of Use  |  Trademarks  |  Privacy Statement
Page view tracker