Saturday, February 25, 2012

Query Notifications in SQL Express?

I am finding conflicting information about enabling query notifications for SQL Express. One book says it can be done and shows an example but most places on MSDN claim it requires the Service Broker. I am using Management Studio for SQL Express which does not show the Service Broker while an installation with SQL Server 2005 Dev Edition does show the Service Broker.

This is all very confusing. I want to be able to develop locally with SQL Express with Query Notifications and later deploy to a server which is more capable. Previously I have read that only real difference between SQL Server 2005 and SQL Express is a 2gb memory limit.

How would I go about setting up Query Notifications for SQL Express? Where would I find such documentation on MSDN?

I am partially past this problem. Upgrading SQL Express to SP1 has fixed this command from handing in Management Studio.

ALTER DATABASE AdventureWorks SET ENABLE_BROKER

Now I can see the Service Broker is enabled, but I guess I cannot really manage it, which is fine as long as I can still use the default queue for notifications. This query confirms it is on.

SELECT name,is_broker_enabled FROM sys.databases

By default the Service Broker is on for new databases but when I load the AdventureWorks database it is not enabled, so I had to get this working. Now I am stuck on these commands...

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO brennan
GRANT SEND ON SERVICE::SqlQueryNotificationService TO brennan

The first one works but the second one fails. It says it does not recognize that service. I would like to know if there is a query I can run to see the available services.

Any help is appreciated.

No comments:

Post a Comment