Troubleshooting Query Notifications: Checking a Subscription

Subscriptions for notification are stored in the database where the query is executed. If your application is not receiving notifications, request a notification and then immediately check to see if the subscription appears in the dynamic management view sys.dm_qn_subscriptions. If SQL Server does not produce a notification event and no notification subscription appears, the parameters for the request are likely invalid. In this case, SQL Server refuses the notification request. SQL Server reports information on refused requests in two places:

• Check the SQL Server error log for errors from the subscription request.

• Use SQL Server Profiler and start a trace that displays the events in the query notifications category. A successful subscription occurs when a QN:Subscription event is produced with an Event SubClass of 1 - Subscription Registered.

Notice that notification subscriptions are tracked using the query and the notification message. When you submit a subscription with the same message, the same query, and the same delivery service as an existing subscription, SQL Server updates the time-out for the existing subscription rather than creating a new subscription.

In general, when a subscription request is invalid, SQL Server immediately produces a notification. The body of the notification message contains additional information about the subscription.