Lesson 11: Submitting Events to the Weather Application

In this step, you will use a Transact-SQL script to submit events to the Weather application, and then view the resulting notifications.

Event Data

Events are data that your notification application uses to generate notifications. The notification generation queries you write join event data and subscription data to produce notifications, such as the query in Lesson 5

The queries that generate notifications are fired during each generator quantum that has data to process. Event rules run in any quantum in which one or more event batches arrived. Scheduled rules run in any quantum that has scheduled subscriptions expected to be processed.

If there is no event data, there is no reason to run rules. Therefore, no notifications are produced.

WeatherData Events

For this application, you will submit a batch of events using the event-submission stored procedures. First, you will use the NSEventBeginBatchWeatherData stored procedure to open an event batch. You will specify the WeatherSPEventProvider non-hosted event provider as the event provider, which you defined in Lesson 6.

-- Use the weather application's database.
USE TutorialWeather;
-- Start an event batch.
DECLARE @BatchID bigint;
EXEC dbo.NSEventBeginBatchWeatherData N'WeatherSPEventProvider', @BatchID OUTPUT;

After creating the event batch, you use the NSEventWriteWeatherData stored procedure to add three weather events. You must provide the event batch ID and then provide values for the event fields. This is one sample event:

-- Submit events.
EXEC dbo.NSEventWriteWeatherData
    @EventBatchId=@BatchID,
    @City=N'Seattle',
    @Date=CONVERT(DATETIME, '2005-04-20 13:00:00', 120),
    @Low = 31,
    @High = 52,
    @Forecast=N'Sunny'

After you use this stored procedure to submit three events, you will use the NSEventFlushBatchWeatherData to close the event batch and submit it to the application database.

-- Flush event batch.
EXEC dbo.NSEventFlushBatchWeatherData @BatchID;

You can display the event batch ID so that you can use views and stored procedures to view information about the event batch.

-- Display event batch ID
SELECT @BatchID 'Event Batch';

Submit Events to the Weather Application

Use the AddWeatherEvents.sql query to add events to the Weather application.

To submit events to the Weather application

1. In Solution Explorer, expand Weather, expand Queries, and then open AddWeatherEvents.sql.

2. Review the Transact-SQL code.

3. Press F5 to run the query.

   You should receive two result sets. The first result set returns an EventCount value of 3. The second result set returns an event batch ID of 1.

View Event Data

Use the ViewWeatherEvents.sql query to view the events you just added.

To view event data

1. In Solution Explorer, open ViewWeatherEvents.sql.

2. Review the Transact-SQL code.

3. Press F5 to run the query.

If you submitted multiple event batches, change the EventBatchId parameter as appropriate.

View Notifications

After about 30 seconds, Notification Services should have created a FileNotifications.htm file in the Notifications folder. Open this file to view the notification data. You should receive three notifications similar to the following:

• Notification Id: 1 Notification Class Name: WeatherNotifications Subscriber Id: stephanie Device Address: stephanie@adventure-works.com Protocol Fields: Body: Weather report for the city of Seattle [2/21/2005 2:01:00 PM]: Sunny
  Expected low temperature : 31 F.
  Expected high temperature: 52 F.
  For more information, visit our web site at http://www.msnbc.com/news/wea\_front.asp?ta=y\&tab=BW\&tp=\&czstr=Seattle
  Thank you for using SQL Server Notification Services.
• Notification Id: 2 Notification Class Name: WeatherNotifications Subscriber Id: david Device Address: david@adventure-works.com Protocol Fields: Body: Weather report for the city of Orlando [2/22/2005 2:01:00 AM]: Partly Cloudy
  Expected low temperature : 59 F.
  Expected high temperature: 81 F.
  For more information, visit our web site at http://www.msnbc.com/news/wea\_front.asp?ta=y\&tab=BW\&tp=\&czstr=Orlando
  Thank you for using SQL Server Notification Services.
• Notification Id: 3 Notification Class Name: WeatherNotifications Subscriber Id: richard Device Address: richard@adventure-works.com Protocol Fields: Body: Weather report for the city of Seattle [2/21/2005 2:01:00 PM]: Sunny
  Expected low temperature : 31 F.
  Expected high temperature: 52 F.
  For more information, visit our web site at http://www.msnbc.com/news/wea\_front.asp?ta=y\&tab=BW\&tp=\&czstr=Seattle
  Thank you for using SQL Server Notification Services.

Because you are using the built-in File delivery protocol, all three notifications generated from the events are in one file. If you submit more events, the additional notifications will be appended to this file.

Also notice that there is a header for each notification. This shows you information about the notification, including the subscriber ID and device address. The actual text of the notification starts after "Body:".

Troubleshooting

If you do not see the FileNotifications.htm file in the Notifications folder within one minute, look in the Application log in Windows Event Viewer for events from Notification Services. The Windows service may not have permission to write to the Notifications folder.

You can view information about any notifications generated for the WeatherNotifications notification class by running the ViewNotifications.sql query.

To view notification information

1. In Solution Explorer, expand Weather, expand Queries, and then double-click ViewNotifications.sql.

2. Press F5 to run the query.

   Look in the DeliveryStatusDescription column for information about notification delivery. If there are any failures, additional information should be provided in the Application log.

3. Close ViewSubscribersAndDevices.sql.

What's Next?

This tutorial has shown you how to build a simple prototype for a Notification Services application. To continue learning about Notification Services, use the following resources:

• Building Notification Solutions
• SQL Server Notification Services Samples

See Also

Concepts

Notification Services Tutorial

Other Resources

Building Notification Solutions
Introducing SQL Server Notification Services

Help and Information

Getting SQL Server 2005 Assistance