代码之家  ›  专栏  ›  技术社区  ›  Ehsan Akbar

目标队列中的消息在SQL Server Service Broker中不可读

  •  0
  • Ehsan Akbar  · 技术社区  · 7 年前

    我正在使用ServiceBroker连接系统中的其他域,如您在此处看到的,我向目标服务发送如下消息:

    _ctx.Database.ExecuteSqlCommand(@"Declare @ConversationHandle uniqueidentifier
    
    Begin Transaction
    Begin Dialog @ConversationHandle
     From Service SenderService
     To Service 'ReceiverService'
     On Contract SampleContract
     WITH Encryption=off;
    SEND 
          ON CONVERSATION @ConversationHandle
          Message Type SenderMessageType
      ('<Reception>Add</Reception><OrganizationId>2</OrganizationId>') Commit");
    

    另一方面,我启用一个SP来获取如下消息:

    ALTER PROCEDURE [dbo].[usp_ProcessTargetQueue] as
    BEGIN
    Declare @ConversationHandle as uniqueidentifier
    Declare @MessageBody as nvarchar(max)
    Declare @MessageType as sysname
    declare @MessageBodyXML as xml
    
    
        WHILE (1=1)
        BEGIN
    
            BEGIN TRANSACTION;
            WAITFOR (
               RECEIVE top (1)
          @MessageType = message_type_name,
          @ConversationHandle = conversation_handle,
        @MessageBody = message_body
    FROM TargetQueue
    
            ), TIMEOUT 5000;
    
           IF (@@ROWCOUNT = 0)
            BEGIN
                  ROLLBACK TRANSACTION;
                  BREAK;
            END
    if @MessageType = 'SenderMessageType'
          BEGIN
                SEND 
                      ON CONVERSATION @ConversationHandle
                      Message Type ReceiverMessageType
                      ('Message is received')
                END Conversation @ConversationHandle
    
            set @MessageBodyXML=    (select cast(@MessageBody as xml)  )
    
          insert into s (s1) select CONVERT(nvarchar(max), @MessageBodyXML)
    
       END
    
    
            COMMIT TRANSACTION;
    
    
    
    
         END  
    
        END
    

    如你所见,我将消息“正文”插入 s1 表,但是当我检查表时,我看不到消息。 enter image description here

    1 回复  |  直到 7 年前
        1
  •  2
  •   Dan Guzman    7 年前

    此代码正在将varchar值插入二进制消息体中:

    SEND 
          ON CONVERSATION @ConversationHandle
          Message Type SenderMessageType
      ('<Reception>Add</Reception><OrganizationId>2</OrganizationId>') Commit");
    

    但这段代码将二进制值转换为 nvarchar 所以这个值被误解了:

    insert into s (s1) select CONVERT(nvarchar(max), @MessageBodyXML)
    

    解决此问题的一种方法是指定 N 前缀,使XML字符串是Unicode文本:

    SEND 
          ON CONVERSATION @ConversationHandle
          Message Type SenderMessageType
      (N'<Reception>Add</Reception><OrganizationId>2</OrganizationId>') Commit");