QueryNotifications.sln is a Visual Studio 2005 project that demonstrates Visual Basic 2005 and SQL Server 2005 T-SQL Windows Form code to implement Query Notifications in standard (SqlDependency) or custom (SqlQueryNotification) mode. It also provides an example of programming Database Mail or SMTP messages in response to Query Notifications. This project is one of three Service Broker examples for my "Program SQL Server 2005's Service Broker" article for Visual Studio Magazine's June 2006 issue. Expert One-on-One Visual Basic 2005 Database Programming's Chapter 10, "Upgrading from SQL Server 2000 to 2005" includes an earilier version of the project and brief instructions on how to use the app's features. The project's VSM version installs in C:\ServiceBrokerQueryNotifications and the book's version installs in C:\VB2005DB\Chapter10\QueryNotifications. Note: The downloadable code from the online version of tne VSM article will be available shortly. This post will be updated when the article goes live. Prerequisites: These instruction assume that you're at least familiar with SQL Server 2005's Service Broker feature as the result of reading the VSM article, the Service Broker content of the book's Chapter 10, or SQL Server Books Online's Service Broker topics. Familiarity with Visual Studio 2005's Query Notifications help topics is also helpful.
Following are the requirements to run the QueryNotifications.sln Visual Basci 2005 project:
- Visual Studio 2005 Standard Edition or Visual Basic Express
- SQL Server 2005 Express or higher (SQL Server 2005 Developer Edition or higher is recommended to enable viewing Service Broker database objects with SQL Server Management Studio's Object Explorer)
- SQL Server 2005 Service Pack 1 (SP 1) Community Technical Preview (CTP) or release version is recommended for SQL Server 2005 and SQL Express
- Northwind sample database installed
- Northiwind database compatibility level = 90 and Service Broker enabled (see below)
- A login with sysadmin privileges (preferably the owner of the Northwind database)
Test Service Broker and SqlDependency Operation
Mark the Enable Query Notifications check box and click the QN Subscriptions button to verify that the subscription was created with this message box:
Select the Invalid Query Test option to verify that the client receives and dequeues SqlDependency messages by opening the following message box:
Click OK to dismiss the message and select the All Products Updates option.
Note: The test query has two elements that conflict with the requiremets for indexed view queries: A TOP 10 option and a missing SCHEMA (dbo) prefix for the Products table. If this message box doesn't appear, messages aren't being received by the client. Check Event Viewer's Applicaton log for SQL Server errors, such as insufficient privileges. You can usually solve these problems by detaching and reattaching the Northwind database with your Windows administrative login as the owner.
Verify an SqlDependency Query Notification
With the All Products Updates option selected, choose a product in the DataGridView, change a UnitsInStock, or UnitsOnOrder value, and click Save Changes to open a message box similar to the following:
Click OK to dismiss the message box, and click Refresh Data. If the change you made results in UnitsInStock + UnitsOnOrder <= ReorderLevel, clicking Refresh Data moves the current row to the selected product, and highlights the ProductName and UnitsInStock cells.
Add an SqlNotificationRequest Subscription and Reorder Message
Using the SqlNotificationRequest class requires substituting custom Service Broker QUEUE and SERVICE objects for the corresponding default objects employed by the SqlDependency class. If you don't provide an activation stored procedure and specify the procedure name when creating the QUEUE object, you must poll the target queue to test for messages in the form of XML documents. This example uses polling.
To add an SqlNotificationRequest for the Reorder Required query and specify a automated reorder message to the product's supplier, select the Reorder Required option, mark the Send Reorder Messages check box, and click Add SqlNotification Objects to open the following message box:
A ROUTE object (ProductsQnRoute) is optional for this example.
Note: SQL Express doesn't support Database Mail, so don't mark the Send by Database Mail check box. If you haven't provided valid service URL, user ID, and password values in the SendReordersBySMTP procedure, the default SMTP mail delivery function will fail.
Click OK to dismiss the message and click QN Subscriptions to verify the additional subscription, as shown here:
Click No to preserve the subscriptions, navigate to ProductID 30 (Nord-Ost Matjeshering), change the UnitsOnOrder value from 10 to 0, and click Save Changes. Click OK to dismiss the SqlDependency message, and click Poll Notifications to display the following message:
If you've configured Database Mail for the SQL Server 2005 instance or SMTP for SQL SErver 2005 or SQL Express, click Yes to send the message to the address you specified as the sender. Here's an example sent to the oakleaf test POP3 server:
Note: Please don't send messages to the above account requesting help with this project. The account is for testing only and all incoming messages are erased periodically.
Otherwise, click No to display this message box with the text of the unsent message:
If you encounter problems running the project, check SQL Server Books Online's "Troubleshooting Service Broker" topic. Check Event Viewer's Application Log for SQL Server/SQL Express errors. Search on "Service Broker" and the Error ID or Event ID value. Good sources of troubleshooting assistance are the SQL Service Broker forum and the microsoft.public.sqlserver newsgroups.
Technorati: Databases SQL Server 2005 SQL Server SQLServer MSSQL SQL Server 2005 Service Broker SQL Server 2005 Query Notifications SQL Server Express SQL Express Query Notifications SqlDependency SqlQueryNotification ASP.NET Cache Invalidation