SQL Dependency on SQL Server 2008 Express

Question

I have an application which successfully uses SqlDependency with SQL Server 2008 Standard Edition. But if I switch the connection string to SQL Server 2008 express (with enabled Broker) it stops working.

I'm not sure whether it is SQL Server Express specific, but which steps should I follow to find out the reason of the problem?

Update. By "stops working" I mean notifications are not raised by SqlDependency

Answer 1

To understand how SQL Dependency works I recommend you read The Mysterious Notification. To troubleshoot it follow the normal Troubleshooting Dialogs steps. The typical culprit is indeed, as in the article you found, the EXECUTE AS context required by Service Broker message delivery. You would see this easily from the transmission_status of the notification messages is sys.transmission_queue and changing the database owner to a valid login (ALTER AUTHORIZATION ON DATABASE::[<dbname>] TO [sa]) will fix it. However there could be other problems, the troubleshooting steps in my link will help you find them.

Answer 2

As @Remus has pointed out, my assumptions based on the misleading documentation were wrong: SQL Server Express does support Service Broker to some extent, despite many online sources stating that it does not support SSB it all. Leaving this up here though so that searches may yield the dialog that ensued.

It seems that SqlDependency relies on Query Notifications. Query notifications in turn rely on Service Broker. Service Broker is not supported in SQL Server Express. This page doesn't list Query Notifications explicitly:

http://msdn.microsoft.com/en-us/library/cc645993(SQL.100).aspx

But from there you can see that Service Broker is supported by Express as a client only, and from the following page you can see that Service Broker is a dependency (no pun intended):

http://msdn.microsoft.com/en-us/library/ms130764(v=SQL.100).aspx