Tuesday, April 11, 2006

Running the Query Notifications Sample Project

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)
Enable Service Broker and Query Notifications for Northwind: Run the following T-SQL script (EnableQueryNotifications.sql in the project folder): USE Northwind; -- Set compatibility level to SQL Server 2005 EXECUTE sp_dbcmptlevel 'Northwind',90; -- Enable Service Broker and set TRUSTWORTHY IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'Northwind' AND is_broker_enabled = 1) BEGIN ALTER DATABASE Northwind SET ENABLE_BROKER WITH ROLLBACK AFTER 5 SECONDS; ALTER DATABASE Northwind SET TRUSTWORTHY ON; END; GO -- Add Database Master Key for Service Broker IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101) CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'23987hxJ#KL95234nl0zBe'; GO -- Settings required for indexed views SET ANSI_NULLS ON; SET ANSI_PADDING ON; SET ANSI_WARNINGS ON; SET CONCAT_NULL_YIELDS_NULL ON; SET QUOTED_IDENTIFIER ON; SET NUMERIC_ROUNDABORT OFF; SET ARITHABORT ON; GO Change the Connection String to suit your SQL Server 2005 instance: The default connection string, stored in My.Settings, is: Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True If you need to change the connection string, open QueryNotifications.sln in Visual Studio, right-click the QueryNotifications project node, choose Properties to open the property pages, click the Settings link, select the Value column of the NorthwindConnection row, and click the builder (...) button to open the Connection Properties dialog. Set the new connection properties, as shown here to substitute an SQL Server Express with the Northwind database installed: Click Test to verify Northwind database connectivity, and click OK to save the new connection string. Execute the project: Open QueryNotifications.sln in Visual Studio and press F5 to complile and run the project. Alternatively, run QueryNotifications.exe from the ...\QueryNotifications\bin\Debug folder. The main form opens with data imported from a local XML data store, as shown here:

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:

Click No to dismiss the message.

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:

Click OK to dismiss the preceding mesage, and click Refresh Data to open this 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:

Troubleshooting

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:

Posted by Roger Jennings (--rj) at 4:13 PM

Labels: ,

Subscribe to: Post Comments (Atom)