Stock Sample
The Stock sample is a Microsoft SQL Server 2005 Notification Services application that uses both event-driven and scheduled subscriptions to produce notifications based on stock market data. Subscribed users receive notifications if the stock price goes above a specified trigger value.
This sample also shows how to host the Notification Services engine in a custom application, instead of using a Microsoft Windows service created by Notification Services.
Scenario
Investors subscribe to notifications about stock prices. Each subscription specifies a stock symbol and is either event-driven or scheduled.
For event-driven subscriptions, when stock events arrive, Notification Services generates a notification if all of the following conditions are met:
- The stock symbol in the event matches the stock symbol in the subscription.
- The new stock price is higher than the subscription's trigger value.
- The previous high price for the stock, which is in a stock event chronicle, is lower than the subscription's trigger value. This ensures that the subscriber does not get multiple notifications for the same stock.
If the new stock price is higher than the price in the chronicle, or if the stock symbol does not exist in the chronicle, Notification Services updates the chronicle.
Notifications for event-driven subscriptions are delivered one at a time.
For scheduled subscriptions, Notification Services matches the data in the stock price chronicle against scheduled subscriptions. A match occurs if the stock symbol in the subscription matches a stock symbol in the chronicle.
For each match, Notification Services produces a notification. The distributor combines multiple notifications for a single subscriber into one notification that provides data about the subscriber's stock portfolio.
Languages
XML, XSD, XSLT, and Microsoft Visual C# or Microsoft Visual Basic.
Features
| Application Area | Features |
|---|---|
Event Class |
One event class and one event chronicle. |
Subscription Class |
Two subscription classes, one for event-driven subscriptions and one for scheduled subscriptions. |
Notification Class |
Two notification classes, one for event-driven notifications and one for scheduled notifications. The scheduled notifications use digest delivery. |
Event Providers |
One file system watcher event provider and one custom, non-hosted event provider. |
Content Formatters |
Notification Services XSLT content formatter. Each notification class specifies its own XSLT file for the content formatter. |
Delivery Protocols |
File and SMTP delivery protocols. |
Notification Services engine |
Hosted in a console application. |
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.
Building the Solution
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
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.
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.
At the command prompt, run the following command to generate the key file:
sn -k SampleKey.snkImportant
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.
Build the solution so that you can start the instance immediately after you create it.
Build the Visual Studio solution
If you are using the.NET Framework SDK, do the following:
- On the Start menu, point to All Programs\Microsoft .NET Framework SDK v2.0, and then click SDK Command Prompt.
- Navigate to the Stock sample's root folder. Type the following command for the default location:
cd C:\Program Files\Microsoft SQL Server\90\Samples\Notification Services\Stock - Type the following to build the solution:
[C#]
msbuild Stock.sln
[Visual Basic]
msbuild Stock_VB.sln
Or, if you are using Visual Studio 2005, do the following:
- Open the solution file of your choice (Stock.sln or Stock_VB.sln).
- Build the solution.
Deploying the Instance of Notification Services
To deploy the instance of Notification Services, you will need 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.
Step 1: Create the instance of Notification Services
Open SQL Server Management Studio and connect to an instance of SQL Server.
In Object Explorer, right-click the Notification Services folder, and then select New Notification Services Instance.
In the New Notification Services Instance dialog box, click Browse and select the InstanceConfig.xml file in the Stock sample's root folder.
In the Parameters box, enter values for the three parameters:
- SampleDirectory is the path of the Stock sample's root folder. The default path is C:\Program Files\Microsoft SQL Server\90\Samples\Notification Services\Stock.
- NotificationServicesHost is the local server name.
- SQLServer is the name of the SQL Server instance.
Select the Enable instance after it is created check box.
Click OK.
When Notification Services has finished creating the instance, click Close.
Step 2: Register the instance of Notification Services
In SQL Server Management Studio, open the Notification Services node, right-click StockInstance, point to Tasks, and then select Register.
Do not select any options. The engine will be hosted by a custom application, so do not create the Windows service.
Click OK.
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, Windows, and folder permissions
In Object Explorer, expand the Security node.
If you need to create a new database login account, right-click Logins and select New Login and create the Login account:
- To use Windows authentication, select Windows authentication, 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.
If the login used to run this sample already has access to SQL Server, right-click the login and select Properties.
In the left pane of the Login dialog box, select User Mapping.
Grant permissions for the StockInstanceNSMain database:
- In the Users mapped to this login box, select StockInstanceNSMain.
- In the Database role membership for: StockInstanceNSMain box, select NSRunService.
Grant permissions for the StockInstanceStock database:
- In the Users mapped to this login box, select StockInstanceStock.
- In the Database role membership for: StockInstanceStock box, select NSRunService.
Click OK to apply SQL Server permissions.
Configure security for the Events folder:
- In Windows Explorer, navigate to the Stock sample's Events folder.
- Right-click the Events folder, select Sharing and Security, and then select the Security tab.
- Click Add and add the account used to run the sample.
- In the Group or user names box, select the account you just added.
- In the Permissions for box, select the Read and Modify permissions.
- Click OK to apply the changes.
Configure security for the Notifications folder:
- Navigate to the Stock sample's Notifications folder.
- Right-click the Notifications folder, select Sharing and Security, and then select the Security tab.
- Click Add and add the account used to run the sample.
- In the Group or user names box, select the account you just added.
- In the Permissions for box, select Write.
- Click OK to apply the changes.
Running the Sample
To run the sample, you need to do the following:
- Start the instance.
- Add subscribers and subscriptions.
- Submit events.
After you perform these tasks, Notification Services will generate notifications. The following procedures show how to do these tasks and how to then view the resulting notifications.
Step 1: Start the hosted engine
Verify that the account under which you are running the hosted engine is a member of the SQLServer2005NotificationServicesUser$ComputerName Windows group, which has permissions to read and execute Notification Services binaries, or that the account has permissions to read and execute files in the C:\Program Files\Microsoft SQL Server\90\Notification Services folder.
In Windows Explorer, navigate to the Stock sample's HostableExecutionEngine\language\HostableExecutionEngine\bin\Debug folder.
Double-click HostableExecutionEngine.exe to run the console application that hosts the Notification Services engine.
Important Do not press the ENTER key. The engine must be running for the Notification Services application to produce notifications.
Step 2: Add subscribers, subscriptions, and events
Run AddSubscribers.exe.
The EXE file is located in the AddSubscribers\language\AddSubscribers\bin\Debug subfolder of the Stock sample.
After the subscribers are added, press the ENTER key to close the console application.
Run AddSubscriptions.exe.
The EXE file is located in the AddSubscriptions\language\AddSubscriptions\bin\Debug subfolder of the Stock sample.
After the subscriptions are added, press the ENTER key to close the console application.
Run NonHostedEventProvider.exe.
The EXE file is located in the NonHostedEventProvider\language\NonHostedEventProvider\bin\Debug subfolder of the Stock sample.
This adds events to the application using a non-hosted event provider, which runs outside of the application. The next step shows how to submit events to a hosted file system watcher event provider.
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 by dropping an event file
In Windows Explorer, navigate to the Stock sample's root folder, and then copy the EventData.xml file to the Events subfolder.
Note
Because you previously submitted events using a non-hosted event provider, this step is optional. If you do not want to submit additional events and generate additional notifications, proceed to the next step to view notifications.
If 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 Application log in Windows Event Viewer for additional details about any errors.
Step 4: View notifications
Give Notification Services approximately one minute to produce notifications.
Using Windows Explorer, navigate to the Stock sample's Notifications folder. You should find a file named FileNotifications.txt. This file contains all notifications sent by this application.
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.
Note
If you want to generate another notification without rebuilding or changing the sample, run the following query: USE [StockInstanceStock] DELETE FROM [StockInstanceStock].[dbo].[StockEventsChron]
Stopping the Engine
When you are done submitting events and viewing the resulting notifications, stop the Notification Services engine.
To stop the engine
- In the HostableExecutionEngine console window, press the ENTER key to stop the engine.
Removing the Sample
Use the following procedure to remove the Stock sample.
To remove the Stock sample
In Object Explorer, expand Notification Services.
Right-click StockInstance, point to Tasks, and then select Unregister.
Right-click StockInstance, point to Tasks, and then select Delete.
See Also
Other Resources
Hosting the Notification Services Engine
SQL Server Notification Services Samples
Securing Notification Services
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
17 July 2006 |
|
14 April 2006 |
|
5 December 2005 |
|