Getting Notifications
Many applications do not need to contain code in order to receive or process query notifications. When the notification subscription is managed by a SqlDependency object, that object automatically monitors the subscription. When a notification message arrives, the SqlDependency object calls the event handler registered in the SqlDependency object. With this approach, there is no special work required to receive the notification. An application that uses SqlDependency does not need to receive or process notification messages.
In contrast, if the application uses a notification request, the application must monitor the queue and react to the notification message. In this case, you write an application that processes messages for the service that receives the notifications. The application that requests the notification may be them same application that processes the message, or you may write another application to receive and react to the query notification message.
SQL Server tracks notification subscriptions using a combination of the notification ID and the actual query submitted. If an application requests a notification for two different queries using the same notification ID, SQL Server creates two subscriptions with the same notification ID. However, if an application requests a notification for the same query with the same notification ID twice, SQL Server creates a single subscription with the time-out specified in the second request.
Applications that run in the database are typically stored procedures activated by the queue when a message arrives. These stored procedures can be written in either Transact-SQL or in one of the .NET languages. Less common approaches include running the application as a scheduled job or using a startup task to run a stored procedure continuously in the background.
Applications that run outside the database typically use one of the following approaches to receive messages:
- The application can poll the queue periodically to see if a message has arrived.
- The application can use the WAITFOR clause of the RECEIVE statement to block the execution of a batch or stored procedure on a RECEIVE statement until the statement returns at least one row.
- The application can create an event notification for the QUEUE_ACTIVATION event on the queue that receives the notification. The application can then monitor the service that receives the activation event using one of the previous two strategies.
Less common approaches include monitoring queue activation using WMI, or writing a common language runtime (CLR) stored procedure that takes some external action in response to a message.
Because query notification dialogs always contain a single notification message, an application that processes query notifications must end the conversation after receiving a message. Otherwise, the dialog eventually times out. When a query notification dialog times out, SQL Server logs the dialog time-out error in the SQL Server error log.
For more information on writing an application that uses Service Broker, see Introduction to Service Broker Programming. For more information on how to start an application that uses Service Broker, see Choosing a Startup Strategy.
See Also
Concepts
Understanding Event Notifications