Query Notification Error

Question

We have this weird issue regarding SQL Query Notification service. We have a main web application running with QN service so the cache item in the web get notification from SQL when the monitoring data changed. And it runs well. What is weird is when the other supporting web service tries to register QN subscription into SQL 2005 SP2 and this error occurs

"System Exception: SqlDependency.Start has been called for the server the command is executing against more than once, but there is no matching server/user/database Start() call for current command "

This error only happen when SQL query in SQLDependency against particular instance DB. The other instance DB works just fine.

There are not much resource can be found for QN / service broker issues. Wish anyone has similar experience can share their knowledge.

Answer 1

This is not a direct answer on the topic, but on the issue of QN/SSB resources I have a few articles on my site:

• http://rusanu.com/2006/06/17/the-mysterious-notification/
• http://rusanu.com/2008/01/04/sqldependencyonchange-callback-timing/
• http://rusanu.com/2007/11/10/when-it-rains-it-pours/
• http://rusanu.com/2005/12/20/troubleshooting-dialogs/

I am a former developer with the SQL Server team and I was involved with both Service Broker and, to more or less degree, with its in-house uses like Query Notifications and SqlDependency, Event Notifications, dbMail etc.

Answer 2

this means that the Start() method has already been called for this DB. the start method has the restrictions you can see here at the bottom.

Answer 3

This is one of those SQL Server subject areas where there are not too many knowledge experts available out there, inluding dare I say, myself ;-)

My suggestion would be to post your query on the official SQL Server Service Broker Forum over at MSDN. Link below for reference:

http://social.msdn.microsoft.com/Forums/en-US/sqlservicebroker/threads