Search code examples
sql-serverservice-broker

My message in target queue is not readable in SQL Server Service Broker

I am using Service broker to connect other domains in my system as you can see here i send a message to target service like this :

_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");

In other hand i enable a SP to get the message like this: 

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

as you see i insert the message_body into s1 table ,but when i check the table i can't see the messages. enter image description here

Solution

  • This code is inserting a varchar value into the binary message_body:

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

    But this code is casting the binary value as nvarchar so the value is misinterpreted:

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

    One way to fix this is to specify the N prefix so the xml string is a Unicode literal:

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