SQL Service Broker 消息中的中文字符

发布于 2024-07-26 19:22:32 字数 1833 浏览 3 评论 0原文

我在数据库中设置了多个 SQL Service Broker 队列，但以前没有遇到过此问题。包含 XML 的消息正在被转换为看起来主要是汉字的内容。当我在将 XML 放入消息队列之前检查存储 XML 的变量时，我可以看到它是英文的并且是良好的 XML 格式。当我从队列中选择时，我收到了汉字。当我使用外部 C# 应用程序从队列中拉取时，我也会收到这些字符。奇怪的是，如果我使用 DBArtisan 查看队列，我会看到格式良好的 XML。

下面的 XML 在放入队列时会转换为下面的中文字符

<?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>

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

下面是我用来将消息放入队列并选择它的 T-SQL。

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;

原文

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;

分享到QQ

分享到微博

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

发布评论

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

疯到世界奔溃 2024-08-02 19:22:32

好的，这个答案是题外话，但我必须：请不要这样做与服务代理一起即发即忘。开始-发送-结束消息模式有很多问题，从无法解决错误响应到数据库脱机。后者是由于 SSB（SQL Service Broker）中的错误造成的，但我已经看到它发生了，它是由即发即忘消息模式引起的。

哦，还有一件事：我不知道 DBArtisan 是什么，但它认为您的 ASCII 消息有效，那么这意味着它将 message_body 列转换为 varchar(max)，仅此而已。

由于我的帖子已经很长了，所以让我也深入了解一下 XML 编码和 SSB。您可能知道，如果您将消息类型 [SAPP_MSG_MessageType] 声明为 VALIDATION = WELL_FORMED_XML，SSB 就会提供 XML 消息验证。但 ASCII 和 UNICODE 都是有效的 XML 编码，并且 SSB 都支持。您可以发送消息N'somecontent'，也可以发送'somecontent' >，两者都是有效的 XML 片段。您还可以添加显式 XML 处理指令来声明编码，例如或 N。但是，您将面临它们不匹配的风险，例如声明 N，这实际上是无效的 XML（因为声明的编码与文档编码不匹配）。您可能会遇到各种非常微妙且难以解决的问题，例如此类。这是一个完美的问题示例，可能会导致目标服务拒绝消息并通过 XML 验证响应使对话框失败，您将会错过这个问题，因为...您正在执行“即发即忘”

:)我的实践（我是 MS SQL Service Broekr 团队的成员之一）我发现避免任何麻烦的最佳方法是将保存发送的 (@msg) 的变量声明为 xml 类型，而不是 varchar 或nvarchar。这可以解决所有问题，并正确解决 XML 中的 BOM。这也适用于传入的 C# 参数，最佳匹配是使用 SqlXml 类型和/或系统.Data.SqlDbType.Xml 枚举值。

同样，在队列的接收端（您激活的过程或读取目标队列的进程），您应该将消息正文转换为 XML 类型，而不是 varchar/nvarchar。当我们讨论这个主题时，请确保您不要在接收期间进行投射，而只能在接收之后进行投射，因为 XML 错误处理与激活交互。

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.

回复收藏 0 原文