SQL Broker service

It basically consists of three parts -:

1)SqlNotificationRequest
2)SqlDependency
3)SqlCacheDependency

Actually the feature in SQL 2005 that delivers notifications on subscriptions for data changes is called Query Notifications

Clients can submit a query requesting to be notified when data was modified in a manner that would change the query result and the server sends a notification when this change occurs. These requests are called ‘query notification subscriptions’.
The list of notification subscriptions can be seen in the server level view sys.dm_qn_subscriptions:
 

select * from sys.dm_qn_subscriptions

whhen you submit a query for the notification, the client submits a service name and a broker instance. Each notification subscription begins a Service Broker dialog with this provided service and broker instance. When data is changed and the change would affect the result of the submitted query result, a message is sent on this dialog. By sending this message, the client is considered notified and the notification subscription is removed. If client desires to be notified on further changes, is supposed to subscribe again.

 

1) SqlNotificationRequest
This is simplest ADO.Net component for subscribing to Query Notifications. This class is used directly to create a query notifications subscription. It consists of three steps -

-         Create a new SqlNotificationRequest object, passing in the appropriate Service Broker service name and broker instance

-         Assign the newly created SqlNotificationRequest to the Notification property of a SqlCommand

-         Execute the SqlCommand.

2)SqlDependency
This component tries to make the task of handling the Query Notification subscription messages as straightforward as possible. Using the SqlDependency the application developer gets a CLR callback whenever data has changed.

3) SqlCacheDependency
This is a component designed specifically for ASP.Net to cache data, using the SqlDependency whenever possible.This is just another implementation of the SQL dependency.

 

Common ERRORs of SQL Broker service

2007-10-12 11:18:32.44 spid25s The query notification dialog on conversation handle ‘{EC54573A-9978-DC11-961C-00188B111155}.’ closed due to the following error: ‘<?xml version=”1.0″?><Error xmlns=”https://schemas.microsoft.com/SQL/ServiceBroker/Error”><Code>-8490</Code><Description>Cannot find the remote service ‘SqlQueryNotificationService-4869b411-fa1c-4d8a-ab37-5bf5762eb98b’ because it does not exist.</Description></Error>’

Reason:

The application is supposed to invoke the static method SqlDependency.
Start  at startup to deploy the necessary infrastructure, then use instances of SqlDependency  object associated with a SqlCommand to receive callbacks when the query executed is notified (data has changed), and finally call SqlDependency.Stop  when the application shuts down to tear down the infrastructure deployed at startup.

The error happens in the following scenario:
1) SqlDependency.Start () is invoked by an application. At this moment a service, a queue and a procedure are created.
2) SqlDependency is used to subscribe to query notifications. Perhaps some queries are notified and re-subscribed, in a normal operations mode
3) Application exists, SqlDependency.Stop is called and the service, queue and procedure are dropped. However, there are still subscribed notifications pending on the server.
4) One or more of the pending subscriptions are notified in the server. This cause a notification message to be sent, but the destination service was dropped, so an error is returned to the sender. The QN receives this error and displays an error message in the ERRORLOG:

A possible workaround would probably have to be based on the kill query notification subscription verb, forcing the application to cleanup any pending subscription at shutdown. Refer to your BOL for more information.