In one of our Enterprise web application, we rely on Sql Server’s Service Broker backed Query Notification feature a lot. Basically the database of that application is exposed to many other client-specific apps, and our app needs to respond to changes being made in the database by the other apps, a perfect use-case for Query Notifications.
However I lately started seeing lots of these errors in our application’s Event Log for one specific client after an update was pushed to them:
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.
It was confusing, as I had checked all Query Notification pre-requisites and everything seemed to be fine on the configuration side. And I was sure SqlDependency.Start was being called on the database we were seeing this error for, before a Notification was being subscribed too.
Googling did not turn up many useful links in this context and so I decided to rip-open System.Data assembly using Reflector. I was able to trace the place where this exception is thrown in SqlDepdency’s internal GetDefaultComposedOptions method. And it appeared that .Net was trying to locate a DataServicePair object in a collection of List<DatabaseServicePair> and for some reason, it was unable to locate it and threw the Exception with the above message.
Having a look at SqlDependency’s private nested DatabaseServicePair class provided a useful hint. That class overrode the Equals method with the following implementation:
{syntaxhighlighter brush: csharp;fontsize: 100; first-line: 1; }public override bool Equals(object value)
{
SqlDependency.DatabaseServicePair pair = (SqlDependency.DatabaseServicePair) value;
bool flag = false;
if (pair == null)
{
return false;
}
if (this == pair)
{
return true;
}
if (this._database == pair._database)
{
flag = true;
}
return flag;
}{/syntaxhighlighter}
And the above implementation clearly depicts that database name comparison is case-sensitive. So it struck me it might be possible that the database names in the connection string that started the SqlDependency and the one that subscribed to Notification might be different (don’t ask me why, this is a complex app which uses a feature we call auto-discovered database, I was able to find a portion of code that had a small probability of having the database name in different cases in an extremely obscure sequence of events).
So I updated the code all over which normalized connection strings to a consistent casing convention when connection strings were generated dynamically in our application. And an update was provided to the client.
It has been a day and I am no longer seeing these errors since the time the update was pushed, which makes me believe that database casing should have been the issue. I would wait for some days (probably a couple of weeks) to conclude database name casing was the issue as the error could be triggered for only a specific sequence of actions in our application. I would provide an update here if we see such errors again.
But if you are facing similar issues with SqlDepdency and Query Notifications, you would want to check out that your connection strings are exactly the same while Starting SqlDependency for a database and when subscribing to a Notification for that database, including casing for “Data Source”, “Username” and “Initial Catalog”/”Database” parameters for the connection string.
Recent Comments