Chinese Characters in SQL Service Broker Message

Question

I have setup multiple SQL Service Broker Queues in a database but have not seen this problem before. A message containing XML is being converted to what appears to be mostly Chinese Characters. When I check the variable that is storing the XML prior to putting it in the message queue I can see that is in English and is well XML formed. When I select from the queue I receive the Chinese Characters. These characters are also what I receive when I pull from the queue with an external C# application. What is strange is that if I view the queue using DBArtisan I see the well formed XML.

The below XML when placed on a queue is transformed into the below Chinese Characters

<?xml version="1.0" ?>
<Message>
  <MachineName>The Super Duper Machine</MachineName>
  <CollectionName>snl0013d</CollectionName>
  <Action>Install</Action>
  <EntryDateTime>Jul  9 2009  4:47PM</EntryDateTime>
</Message>

---

㼼浸敶獲潩㵮ㄢ〮•㸿਍†††䴼獥慳敧ാ
 †††㰠慍档湩乥浡㹥桔⁥畓数⁲畄数⁲慍档湩㱥䴯捡楨敮慎敭ാ
 †††㰠潃汬捥楴湯慎敭猾汮〰㌱㱤䌯汯敬瑣潩乮浡㹥਍
 ††††䄼瑣潩㹮湉瑳污㱬䄯瑣潩㹮
 ਍††††䔼瑮祲慄整楔敭䨾汵†‹〲㤰†㨵〱䵐⼼湅牴䑹瑡呥浩㹥਍
†††⼼敍獳条㹥

Below is the T-SQL I am using to put the message on the queue and select it.

declare @dialog_handle uniqueidentifier
    ,@msg varchar(max)
    ,@collection_name varchar(30)

set @collection_name = 'snl0013d'

set @msg =
N'<?xml version="1.0" ?>
  <Message>
    <MachineName>' + 'The Super Duper Machine' + '</MachineName>
    <CollectionName>' + @collection_name + '</CollectionName>
    <Action>' + 'Install' + '</Action>
    <EntryDateTime>' + CAST(getdate() AS VARCHAR(100)) + '</EntryDateTime>
  </Message>'

select @msg

set @dialog_handle = NEWID()
begin dialog conversation @dialog_handle
 from service [SAPP_QUEUE_ResponseService]
 to service 'SAPP_QUEUE_SubmitService'
 on contract [SAPP_CONTRACT_Contract]
 with encryption = off;

send on conversation @dialog_handle
  message type [SAPP_MSG_MessageType]
(
 @msg
);
end conversation @dialog_handle 
with cleanup

select message_body
   ,conversation_handle
   ,CONVERT(nvarchar(max), message_body) as msg
  from SAPP_QUEUE_SubmitQueue;

Answer 1

I don't know if that's it, but I see you using nvarchar literals but assigning to varchar variables...

Answer 2

OK, this answer is off topic but I must: please don't do fire and forget with service broker. The begin-send-end message pattern has many problems, ranging from not being able to troubleshot error responses to your database being taken offline. The later is due to a bug in SSB (SQL Service Broker), but I've seen it happen and it is caused by fire and forget message pattern.

Oh, and one more thing: I don't know what DBArtisan is, but it sees your ASCII message as valid then it means it casts the message_body column to varchar(max), that's all.

And since my post is already big, let me drill a bit into XML encoding and SSB too. As you probably know, SSB offers XML message validation if you declare the message type [SAPP_MSG_MessageType] as VALIDATION = WELL_FORMED_XML. But ASCII and UNICODE are both valid XML encodings and both are supported by SSB. You can send a message N'<someTag>somecontent</someTag>' and also '<someTag>somecontent</someTag>', both are valid XML snippets. You can also add explicit XML processing instructions declaring the encoding, like <?xml version="1.0" encoding="utf-8"?> or N<?xml version="1.0" encoding="utf-16"?>. However you will run the risk of mismatching them, like declaring N<?xml version="1.0" encoding="utf-8"?>, which is actually invalid XML (since the declared encoding does not match the document encoding). You can run into all sort of very subtle and hard to troubleshoot issues like this. This is a perfect example of problem that can cause the target service to reject the message and fail the dialog with an XML validation response, which you're going to miss because... you're doing fire-and-forget :)

In my practice (I was one of the members of the SQL Service Broekr team at MS) I found that the best way to avoid any trouble is to declare the variables holding the sent (@msg) as type xml, not varchar nor nvarchar. This takes care of all the issues and also correctly addresses the need and presence of a BOM in your XML. This applies also to the C# parameters passed in, the best match is to use the SqlXml type and/or the System.Data.SqlDbType.Xml enum value.

Also on the receive side of the queue (your activated procedure or process that reads the target queue) your should cast the message body to XML type, not to varchar/nvarchar. And while we're on the subject, make sure you don't cast during the receive, but only after the receive because of the way XML error handling interacts with activation.