sql-serversql-server-expresssqldependency

SQL Dependency on SQL Server 2008 Express


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


Solution

  • To understand how SQL Dependency works, I recommend that 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 in 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.