Troubleshooting Query Notifications: Subscription Registered, Event Message Not Produced, Subscription Removed
If a subscription is successfully registered, but a query that should produce a notification does not produce a notification even though the subscription is removed, SQL Server cannot deliver the event message to the specified service. In this case, the statement that updated the data may return an error, or Service Broker may send an error to the QueryNotificationErrorsQueue. SQL Server receives these error messages and writes the errors to the SQL Server error log.
The most common cause of this problem is that the subscription request did not contain a service name that matched a service in the database.
If the command that changes the data does not report an error, the most likely problem is that the service that receives the request is not correctly configured. To diagnose the problem, check the SQL Server error log.
You can also confirm whether SQL Server produced a notification message using SQL Server Profiler. In SQL Server Profiler, start a trace that displays the events in the Query Notifications category. When SQL Server produces a notification message, it also produces a QN:Subscription event with an Event SubClass of 3 - Subscription Fired.
For more information on troubleshooting Service Broker routing and message delivery, see Troubleshooting Routing and Message Delivery.
See Also
Other Resources
sys.services (Transact-SQL)
Troubleshooting Routing and Message Delivery
SQL Server Profiler Reference