SQL Server 2008 Express 上的 SQL 依赖 [英] SQL Dependency on SQL Server 2008 Express

问题描述

我有一个应用程序，它成功地将 SqlDependency 与 SQL Server 2008 标准版一起使用.但是，如果我将连接字符串切换到 SQL Server 2008 express(启用了 Broker)，它就会停止工作.

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.

我不确定它是否特定于 SQL Server Express，但是我应该按照哪些步骤来找出问题的原因?

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?

更新.停止工作"是指 SqlDependency 不会引发通知

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

推荐答案

要了解 SQL Dependency 的工作原理，建议您阅读 神秘通知.要对其进行故障排除，请按照正常的对话框故障排除步骤操作.正如您在文章中发现的那样，典型的罪魁祸首确实是 Service Broker 消息传递所需的 EXECUTE AS 上下文.您可以从 sys.transmission_queue 并将数据库所有者更改为有效登录名(ALTER AUTHORIZATION ON DATABASE::[] TO [sa]) 将修复它.但是，可能还有其他问题.我链接中的故障排除步骤将帮助您找到它们.

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.

这篇关于SQL Server 2008 Express 上的 SQL 依赖的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！