42

I've been using SqlDependency in a .NET 3.5 application for over 3 years without any problems. The scenario is as follows:

  1. Central windows service with a SqlDependency watching a table (let's call this Table A)
  2. Multiple users using a WPF application with a unique SqlDependency per user watching a table (Table A again)
  3. User queues a unit of work for processing in Table A
  4. SqlDependency fires on windows service
  5. Windows service processes all outstanding items in Table A and saves results to Table A
  6. Unique SqlDependency fires for each user who's results are available
  7. User processes their work

Recently I've upgraded the system to .NET 4.5 and started seeing issues where changes would be put into the table we're monitoring, but the SqlDependency would never fire (both in the service and in the user application). I started digging into this further and noticed in my logs that at some point I would re-register the SqlDependency, but then the notification would never fire after that.

After seeing this behavior, I decided to run the profiler on SQL server to capture subscription events. From the data that was captured, I noticed that sometimes a subscription would be registered (with a unique ID) by one user but then it would be fired (with the same unique ID) by another user. This typically happens with the service I mentioned above and one or more of the users of the WPF application. (I've attached a screenshot of the the issue in the profiler results)

Is this expected behavior? That a notification can be fired for a different user than it was registered by? Does this point to a problem in the application layer? Any help is appreciated.

Profiler Results

Sean
  • 60,939
  • 11
  • 97
  • 136
Michael Bowersox
  • 1,471
  • 11
  • 17
  • 3
    Wanted to add a comment... I've opened a ticket w/ Microsoft support and they have told me that the data I'm seeing the SQL profiler is correct. In the trace, we see that the subscription was fired with a different user than created it. This is expected behavior because this is the user that executed the query which fired the subscription, not the user that the subscription was fired for. – Michael Bowersox Nov 27 '13 at 15:20
  • 1
    So did that solve your issue? We're about to use SqlDependency for the first time in this exact same manor in .net 4.5 so I'm researching it. – Johnathon Sullinger Jul 01 '15 at 16:03
  • Is broker enabled for your database? Do you have both the Queue and the service configured correctly for your database? Is your service user and target clients have sufficient permission to receive notifications? – Hesham Jan 14 '16 at 07:49
  • Might sound silly, but have you tried stopping and starting the service broker? – Balah Jan 23 '16 at 20:47
  • A long shot, but what if the service you are talking about crashes and restarts? Would that explain the behaviour in any way? – ikwillem Feb 18 '16 at 09:24
  • As a general rule, I avoid using one subscription per user designs. I haven't had your particular problem, but Microsoft warns against having too many listeners without defining "too many". – Jonathan Allen Mar 05 '16 at 04:26

1 Answers1

4

Since multiple SqlDependency instances are being created, I would check for Duplicate Subscriptions, as outlined here. The subscriptions may be considered duplicates depending on your configuration. The behavior "means that if a notification is requested for identical queries, only one notification is sent."

Another thing to check is whether the OnChange event could actually be working, but not appear so due to it firing on a different thread. The multi-threaded nature of SqlDependency is noted here.

The Profiler trace indicates the Service Broker is doing its job. Adding additional tracing in the .NET tiers would show if the subscription is firing at the database level, but not resulting in the OnChange event firing. Additional troubleshooting tips can be found here.

Christopher J. Grace
  • 431
  • 3
  • 16