SQL 依赖项事件会在 Alter Database 上触发，但不会在表更改时触发
处理别人的代码，他们已经建立了 SQL 依赖关系事件 (SQL 2008)。这不是我非常熟悉的东西，所以只是做了一些阅读和一些测试。
该事件称为 CustomerServicesTable_OnChange，因此可能会在 customerServices 表发生更改时触发。但是，当通过代码更改数据库时（我们使用实体框架），该事件不会触发。
出于好奇，我启动了 SQL Server Manager，并尝试直接对表运行查询，看看会发生什么。对表运行 UPDATE、INSERT 或 ALTER 查询不会导致事件触发。
为了检查 SQL 依赖项是否已启用，我对数据库运行了以下命令，这应该是启用该服务的第一步：
ALTER DATABASE customers SET SINGLE_USER with rollback immediate
alter database customers set enable_broker
ALTER DATABASE customers SET MULTI_USER
令我惊讶的是，运行这些命令导致事件触发！
因此，看起来依赖关系要么针对整个数据库，要么针对除 CustomerServices 之外的数据库中的表。谁能建议一种前进的方法来帮助我准确地确定问题是什么？
编辑：我发现不是 ALTER DATABASE 命令导致事件触发，而是 SET SINGLE_USER 导致错误。但对我解决问题没有多大帮助。
干杯， 马特
Working on someone else's code where they've instituted a SQL Dependency event (SQL 2008). Not something I'm terribly familiar with so was just doing some reading and some testing.
The event is called CustomerServicesTable_OnChange and so is presumably meant to fire whenever the customerServices table is changed. However when the DB is changed through code (we're using Entity Framework) the event is not firing.
Out of curiosity I fired up SQL Server Manager and tried running queries against the table directly to see what would happen. Running UPDATE, INSERT or ALTER queries against the table didn't cause the event to fire.
To check that SQL dependency had been enabled I then ran the following commands against the database, supposedly the first step in enabling the service:
ALTER DATABASE customers SET SINGLE_USER with rollback immediate
alter database customers set enable_broker
ALTER DATABASE customers SET MULTI_USER
To my surprise running these commands caused the event to fire!
So it looks as though the dependency is working against either the database as a whole, or table(s) in the DB other than CustomerServices. Can anyone suggest a way forward to help me determine exactly what the problem is?
EDIT: I have discovered that it's not the ALTER DATABASE commands which are causing the event to fire, but the fact that SET SINGLE_USER causes an error. Doesn't help me with the problem much though.
Cheers,
Matt
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
终于解决了这个问题：
1）数据库所有者需要设置为 SQL Server 帐户而不是 Windows 管理员帐户。
2) 监视服务（即发出来启动通知服务的 SQL 命令）需要在用于进行您正在监视的数据库更新的同一用户帐户上设置。
我做了很多权限工作，但我认为这是使其发挥作用的两个关键问题。
Finally got this one worked out:
1) The database owner needs to be set to a SQL Server account instead of a Windows Admin account.
2) The watch service (i.e. the SQL command issued to start the notification service) needs to be set up on the same user account as will be used to make the database updates you're watching for.
I did a bunch of permissions work, but I think those are the two key issues to making it work.
首先阅读本文以了解 SqlDependency 的工作原理：神秘通知。
通知可能堆积在
sys.transmission_queue和transmission_status将指示发生这种情况的原因。黑暗中的一枪：dbo 被映射到无效的 SID：
Read this first to understand how SqlDependency works: The Mysterious Notification.
Possibly the notifications are piling up in
sys.transmission_queueand thetransmission_statuswould indicate why this happens.A shot in the dark: the dbo is mapped to invalid SID: