21 September 2004 12:36 nielsb

SqlDependency


In the Whidbey release of ADO.NET is the SqlDependency class. This class allows the user to set up a callback which is called when the underlying data changes. Bob wrote about it here.

Even if the "rules" Bob stated are followed you may not get it to work, and there are two main culprits for this:

  • Guest user has to be granted send rights on the SqlQueryNotificationService in the MSDB database.

This is as the dependency class under the covers uses Service Broker and posts a message to the  SqlQueryNotificationService when changes happen. Syntax to grant send rights are (in the MSDB database):

GRANT SEND ON SERVICE::SqlQueryNotificationService to GUEST

The second thing is:

  • if you try this in the AdventureWorks database it won't work because Service Broker is not enabled by default (however if you create a new database, SSB is enabled).

You can check whether SSB is enabled by using the DATABASEPROPERTYEX function like so:

select databasepropertyex('AdventureWorks', 'IsBrokerEnabled')

It returns 1 for true and 0 for false. To enable SSB you run:

ALTER DATABASE AdventureWorks SET ENABLE_BROKER

I believe that's it. Have Fun!!


Filed under:

Comments

# re: SqlDependency

30 January 2008 03:31 by Nitin

Gr8 work. Thanx