THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Limitations of the SqlDependency in ADO.NET

One of the goals that I have in building the Extended Events Manager is that it would be able to work around the fact that Extended Events currently do not have a streaming target available where the event sessions can be subscribed to for real time consumption of events similar to SQL Trace and how SQL Profiler displays data in a active session.  Initially I figured that this would be really easy to do, using a SqlDependency and a query for the target against the sys.dm_xe_session_targets DMV.

For those unfamiliar with the SqlDependency class, it was an addition to ADO.NET 2.0 that allows you to create a dependency against the data for a SqlCommand.  It uses Service Broker in SQL 2005 and posts a message to the SqlQueryNotificationService when changes happen to the underlying data for the SqlCommand.  Its primary purpose is for ASP.NET data caching, and other server/service type scenarios where it is necessary for the server/service to know when changes occur to data inside SQL Server.  It is not generally recommended to use SqlDependency in client applications since each client would be registering the SqlDependency which would impact performance.

Since the Extended Events Manager is a DBA tool, I figured making use of this class would be fine as long as I followed good programming practices and disposed of the dependency properly when it was no longer needed.  I've used the SqlDependency object in the past to show people how to create services external to SQL Server rather than creating UNSAFE SQLCLR objects that don't actually belong in SQL Server inside of SQL Server.  However, one thing I never actually noticed was that there are numerous restrictions on the SqlDependency until I started trying to wire up the code for use in the Extended Events Manager.

After a many hours of frustration debugging with SQL Trace, and .NET breakpoints in code, I came to the conclusion that it must be impossible to work with DMV's and the SqlDependency.  I was positive that my code was correct, and with a bit of searching I found the following BOL Reference:

Special Considerations When Using Query Notifications

Rather than copy the entire list of restrictions, here are the two that applied to my problems:

Query notifications are supported for SELECT statements that meet the following requirements:

  • The statement must reference a base table.
  • The statement must not reference system tables or views, including catalog views and dynamic management views.

Pretty cut and clear on that point, so now I am off to devise another way to make this work.

Published Monday, February 16, 2009 4:05 AM by Jonathan Kehayias



sqlgeek said:


February 16, 2009 9:55 AM
Anonymous comments are disabled

This Blog


Privacy Statement