使用 Sql Server 2005 的 Service Broker - 消息卡在队列中

发布于 07-27 04:06 字数 2877 浏览 7 评论 0原文

我正在配置一个简单的服务代理实现。 在本地,我们使用 SQL Server 2008 Express,一切正常。 一旦代码在我们的生产服务器 (SQL SERVER 2005) 上运行,消息就会滞留在接收队列中。 以下代码不完整,但说明了队列和服务的基本配置方式:

-- Create message type to validate the content of a message
CREATE MESSAGE TYPE MyMessageType VALIDATION = NONE;

-- Create contract to validate what direction messages can be sent in a conversation.
CREATE CONTRACT MyContract 
(
    MyMessageType SENT BY INITIATOR
) 

-- The receiver queue will process each message using the 'spProcessMessage' stored procedure
CREATE QUEUE [MyReceiverQueue];
ALTER QUEUE [MyReceiverQueue] WITH ACTIVATION 
(
      STATUS = ON,
      MAX_QUEUE_READERS = 1,
      PROCEDURE_NAME = spProcessMessage,
      EXECUTE AS SELF
);

-- The receiver service processes the incoming messages and passes them to the ReceiverQueue.
CREATE SERVICE [MyReceiverService] ON QUEUE [MyReceiverQueue]([MyContract]);

-- Queue and service to send the message from
CREATE QUEUE [MySenderQueue];
CREATE SERVICE [MySenderService] ON QUEUE [MySenderQueue];

安装服务后,将以这种方式触发消息:

-- Send a message to the receiver queue
DECLARE @MessageBody XML
SET @MessageBody = ''; 
DECLARE @Handle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @Handle
FROM SERVICE [MySenderService]
TO SERVICE 'MyReceiverQueue'
ON CONTRACT [MyContract]
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @Handle 
MESSAGE TYPE [MyMessageType](@MessageBody);

所有消息都卡在“MyReceiverQueue”中。 如果我手动执行“spProcessMessage”,消息将正常处理。

更多细节:
- sys.transmission_queue 为空
- sys.conversation_endpoints 声明这两个服务都是“CONVERSING”
- 代理已启用,数据库配置为与 SQL 2005 兼容

知道为什么这些消息不会自动处理吗?

非常感谢您抽出时间。

--

好的,经过一番尝试后,我开始使用 ssbdiagnose 了。 正如 Remus 所说,它位于:C:\Program Files\Microsoft SQL Server\100\Tools\Binn。 我以这种方式使其在本地工作:

ssbdiagnose -E -S "JDECUPER\SQLEXPRESS" -d mydb CONFIGURATION FROM SERVICE MySenderService TO SERVICE MyReceiverService ON CONTRACT MyContract

然后,我在生产服务器上尝试了它:

ssbdiagnose -XML -U loginID -P pwd -S "machine's IP" -d mydb CONFIGURATION FROM SERVICE MySenderService TO SERVICE MyReceiverService ON CONTRACT MyContract > C:\testBrokerService.xml

检测到第一个错误

Service Broker GUID is identical to that of database Pandilla on server 200.57.141.193

服务器上的另一个数据库具有与服务代理相同的 GUID。 我只需重新生成 GUID:

ALTER DATABASE [myotherdb] SET NEW_BROKER;

第二个错误用户执行存储过程的权限有关:

The EXECUTE AS for the user dbo specified for activation on queue dbo.AlbumGanadoresQueue cannot be impersonated due to an exception

升级这些权限后,一切都开始正常工作。

非常感谢 Remus 为我指明了正确的方向。 我说的有点长,但我希望详细信息能够帮助其他开发人员。

I'm configuring a simple service broker implementation. Locally, we use SQL Server 2008 Express and everything works fine. Once the code runs on our production server (SQL SERVER 2005), messages are stuck in the receiver queue. The followind code is incomplete but states how queues and services are basically configured:

-- Create message type to validate the content of a message
CREATE MESSAGE TYPE MyMessageType VALIDATION = NONE;

-- Create contract to validate what direction messages can be sent in a conversation.
CREATE CONTRACT MyContract 
(
    MyMessageType SENT BY INITIATOR
) 

-- The receiver queue will process each message using the 'spProcessMessage' stored procedure
CREATE QUEUE [MyReceiverQueue];
ALTER QUEUE [MyReceiverQueue] WITH ACTIVATION 
(
      STATUS = ON,
      MAX_QUEUE_READERS = 1,
      PROCEDURE_NAME = spProcessMessage,
      EXECUTE AS SELF
);

-- The receiver service processes the incoming messages and passes them to the ReceiverQueue.
CREATE SERVICE [MyReceiverService] ON QUEUE [MyReceiverQueue]([MyContract]);

-- Queue and service to send the message from
CREATE QUEUE [MySenderQueue];
CREATE SERVICE [MySenderService] ON QUEUE [MySenderQueue];

Once the service is installed, a message is fired this way:

-- Send a message to the receiver queue
DECLARE @MessageBody XML
SET @MessageBody = ''; 
DECLARE @Handle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @Handle
FROM SERVICE [MySenderService]
TO SERVICE 'MyReceiverQueue'
ON CONTRACT [MyContract]
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @Handle 
MESSAGE TYPE [MyMessageType](@MessageBody);

All message are stuck in the 'MyReceiverQueue'. If I execute manually 'spProcessMessage', messages are processed normally.

A few more details:
- sys.transmission_queue is empty
- sys.conversation_endpoints state that both service are 'CONVERSING'
- Broker is enabled and DB is configured as SQL 2005 compatible

Any ideas why those messages aren't processed automatically?

Thanks a lot for your time.

--

OK, after playing around for a bit I got ssbdiagnose working. As Remus commentented, it is located here: C:\Program Files\Microsoft SQL Server\100\Tools\Binn. I made it work locally this way:

ssbdiagnose -E -S "JDECUPER\SQLEXPRESS" -d mydb CONFIGURATION FROM SERVICE MySenderService TO SERVICE MyReceiverService ON CONTRACT MyContract

Then, I tried it on our production server:

ssbdiagnose -XML -U loginID -P pwd -S "machine's IP" -d mydb CONFIGURATION FROM SERVICE MySenderService TO SERVICE MyReceiverService ON CONTRACT MyContract > C:\testBrokerService.xml

First error detected:

Service Broker GUID is identical to that of database Pandilla on server 200.57.141.193

Another database on the server had an identical GUID for the service broker. I only had to regenerate the GUID:

ALTER DATABASE [myotherdb] SET NEW_BROKER;

The second error had to do with the user's permissions to execute the stored procedure:

The EXECUTE AS for the user dbo specified for activation on queue dbo.AlbumGanadoresQueue cannot be impersonated due to an exception

After upgrading those permissions, everything started to work correctly.

Many thanks to Remus for me pointing into the right direction. I was a bit long but I hope the details will help other developers.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

夜雨飘雪2024-08-03 04:06:55

首先阅读我网站上的故障排除对话框指南,然后按照以下步骤操作步骤直到找到可能的问题。
其次,如果您有权访问 SQL 2008 部署,请运行

First thing read up the Troubleshooting Dialogs guide on my site and follow the step by step until you find the possible issue.
Second, if you have access to a SQL 2008 deployment, run the ssbdiagnose tool from it. Although is part of SQL 2008, is perfectly capable of diagnosing SQL 2005 as well. This is the preferred method, again, if you have access to a SQL 2k8 deployment.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文