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.