Follow me on Twitter at
FYI, I'm blogging most of my stuff over at More Wally now.
You might want to add my rss feed to your reader at:
Sql Dependency rules in .NET 2.0 Whidbey - Wallace B. McClure

Wallace B. McClure

All About Wally McClure - The musings of Wallym on Web, HTML5, Mobile, Xamarin.iOS, Xamarin.Android, and Windows Azure.


Visual Studio Magazine Column Personal Blog


Book Authors




Georgia Tech Bloggers



Sql Dependency rules in .NET 2.0 Whidbey

There are a couple of rules that I have been posted in the newsgroups that I wanted to post.  "Select * from Table" will not work with the SqlDependency object.

  • You must specify the columns that you want to use.
  • The schema/owner must be specified.
  • The data must be queried after the dependency is created and defined.
  • Service Broker must be running on that database.  This can be checked by calling "Select databasepropertyex('db Name', 'IsBrokerEnabled')".  A '1' means that the broker is enabled.  A '0' means that the broker is not enabled.  This can be turned on by calling. "ALTER DATABASE dbName SET ENABLE_BROKER".
  • "GRANT SEND ON SERVICE :: SqlQueryNotificationService to GUEST" in the msdb database.
  • Once the OnChanged event is fired, you must rebuild the SqlDependency object.

Use this select command "select col1, col2 from dbo.Table" as appropiate.

I am looking for more rules.  If you have them or know them, let me know.

Thanks to Niels Berglund, Marcel Gnoth(?), Bjorn Backlund, and Pablo Castro for their posts about this in the ADO.NET Whidbey newsgroup.



Neelesh said:

When I execute GRANT SEND ON SERVICE :: SqlQueryNotificationService to GUEST, I get the error...

Msg 15151, Level 16, State 1, Line 1

Cannot find the service 'SqlQueryNotificationService', because it does not exist or you do not have permission.

Could you please help.

# July 1, 2008 1:41 PM