SqlDependency not registering subscriptions only with MY database

Question

Hi,

I have been trying to use the SqlDependency class to register a subscription with my SQL database from .Net code. I have ensure that my login has all of the required permissions and that my DB has the Service Broker enabled. When my code runs, a new Queue and Service are created, but no subscription is registered (i.e. nothing new shows up in sys.dm_qn_subscriptions).

Out of desperation, I tried set up AdventureWorks and created the project detailed in the MSDN article 'Using SQLDependency in a Windows Application' (http://msdn.microsoft.com/en-us/library/a52dhwx7%28VS.80%29.aspx) and it works! However, when I modify the connection string to connect to my database, and change the SELECT statement to pull something from one of my tables, the data is retrieved but no subscription is registered!

This is driving me crazy. From my debugging, I have concluded that the problem must be something with my database, but i can't figure it out for the life of me.

My SQL Server is local and I am sa. I am using stock .Net front-end code : SqlDependency with the SqlCommand class.

Does anybody have any ideas?

Thanks in advance,

Chris

Answer 1

Hi Chris,

With the amount of information you've provided, I'd guess that it was
something wrong with the query you're trying to register a notification on,
and you're getting an error message back on "OnChanged". The most common
problems are queries that use "select *" and one-part database object names
(both are not allowed with query notificatons). The error information can be
s part of the SqlNotificationEventArgs method. Additional information
can be found here: http://msdn.microsoft.com/en-us/library/ms189308.aspx

Hope this helps,
Bob Beauchemin
SQLskills

Answer 2

Thanks a lot Bob!

You were right... I was using 'SELECT *' in my queries...
Unfortunately I didn't check this thread until after I figured it out after a marathon SQL Profiler session, and then groaning when I realized I had already read the MSDN article detailing SQL statement restrictions (but apparently not carefully enough).

Thanks again

Chris