SQL Server 2014 Service Broker 未激活从队列接收消息的过程 [英] SQL Server 2014 Service Broker does not activate the procedure that receives messages from the queue

问题描述

这种情况与SQL Service Broker - 通信场景 - 从 SQL 2008 R2 迁移到 SQL 2014 和SQL Service Broker -- 一个中央 SQL 和多个卫星 SQL...初学者想了解细节.

从 SQL Server 2008 R2 标准版迁移后.到 SQL Server 2014 标准版，相同的代码不起作用.防火墙设置为允许通信.

After migration from SQL Server 2008 R2 Standard Ed. to SQL Server 2014 Standard Ed., the same code does not work. The firewall was set to allow the communication.

sys.transmission_queue(在发送方和接收方服务器上)保持为空，GenericQueue(我的队列标识符)接收消息.但是，由(到接收 SQL 服务器)附加的过程:

The sys.transmission_queue (on both sender and receiver servers) keeps empty, and the GenericQueue (my identifier for the queue) receives the messages. However, the procedure attached by (to the receiving SQL server):

ALTER QUEUE [GenericQueue]
    WITH ACTIVATION (
    STATUS = ON,
    MAX_QUEUE_READERS = 1,
    PROCEDURE_NAME = [usp_CentralActivation],
    EXECUTE AS OWNER);

未激活.我已经把日志消息放在里面有确凿的证据——程序没有被调用.

is not activated. I have put the log message inside to have the tangible proof -- the procedure is not called.

我没有观察到任何错误消息或指示——或者我不知道在哪里寻找指示.我怎样才能找到问题所在?我应该在此处发布哪些信息以帮助查找原因?

I do not observe any error message or indication -- or I do not know where to look for the indication. How can I find what is the problem? What information should I post here to help to find the reason?

安装服务代理的代码是从模板生成的，除了机器标识(IP 地址作为字符串)之外，完全相同的代码在 SQL Server 2008 R2 上运行良好.

The code that installs the service broker is generated from templates, and besides the machine identification (IP address as a string) the exact same code worked nicely on SQL Server 2008 R2.

EXECUTE AS OWNER 可能是原因吗?谁是所有者?

Could the EXECUTE AS OWNER be the reason? Who is the OWNER?

推荐答案

黑暗中的一击.尝试运行这个:

A shot in the dark. Try running this:

EXECUTE AS USER = 'dbo';

如果失败，则数据库所有者 SID 在本地无效(在还原或文件复制后经常发生).解决方案很简单:

If this fails, then the database owner SID is invalid locally (happens frequently after a restore or file copy). The solution is trivial:

ALTER AUTHORIZATION ON DATABASE::<dbname> TO [sa];

如果您仍有问题，请查看了解队列监视器.查看 sys.dm_broker_queue_monitors 并看到 1) 队列存在和 2) 状态为 RECEIVES_OCCURING

If you still have problems then have a look at Understanding Queue Monitors. Look in sys.dm_broker_queue_monitors and see that 1) the queue is present and 2) the status is RECEIVES_OCCURING

这篇关于SQL Server 2014 Service Broker 未激活从队列接收消息的过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！