Query Notification Error

Question

Hi, 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 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

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 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 4

Remus , Here is the call stack for the exception. I am sure app already start the Sqldependency listener against the table where it tries to register QN subscription. Otherwise , it should be another kind of error message. There is another user post the same question on MSDN but no response saying that he got this error "sometimes" .. Thanks

INNER EXCEPTION 1 Source:System.Data Message: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. Call Stack: at System.Data.SqlClient.SqlDependency.GetDefaultComposedOptions(String server, String failoverServer, IdentityUserNamePair identityUser, String database) at System.Data.SqlClient.SqlCommand.CheckNotificationStateAndAutoEnlist() at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at DLM.DAL.SqlCacheExpiration..ctor(DBConnections& DBConnect, String cmdtext, DBType DBType) ......