Follow me on Twitter at Twitter.com/wbm
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:http://morewally.com/cs/blogs/wallym/rss.aspx
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.

News

Visual Studio Magazine Column Personal Blog

.NET

Book Authors

Business

Family

Friends

Georgia Tech Bloggers

Personal

Archives

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.

Wally

Comments

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