I posted this same question over at stack overflow and haven't received a response yet so I'm hoping to find some help here.
I've been using SqlDependency in a .NET 3.5 application for over 3 years without any problems. The scenario is as follows:
- Central windows service with a SqlDependency watching a table (let's call this Table A)
- Multiple users using a WPF application with a unique SqlDependency per user watching a table (Table A again)
- User queues a unit of work for processing in Table A
- SqlDependency fires on windows service
- Windows service processes all outstanding items in Table A and saves results to Table A
- Unique SqlDependency fires for each user who's results are available
- 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.