上下文 sp_OAMethod 'send' 中的 msxml3.dll [英] msxml3.dll in context sp_OAMethod 'send'

问题描述

Win2003 + SQL Server 2005 的工作代码在 Win2012 + SQL Server 2012 sp1 下不起作用.

Working code from Win2003 + SQL Server 2005 is not working under Win2012 + SQL Server 2012 sp1.

唯一的 ~real 解决方案我发现是:

The only ~real solution I found is:

我将 C:WindowsSystem32msxml3.dll 从 Server 2008 复制到相同的2012 年服务器上的目录.2012 年服务器上的问题已解决，使用 POST 发送并且 GET 工作正常.

I copied C:WindowsSystem32msxml3.dll from a Server 2008 to the same dir on a server 2012. Problem on 2012 server solved, sending with POST and GET working fine.

但是由于我无法修改服务器并且 msxml3.dll 和 msxml6.dll 都被锁定 - 我需要了解什么是错误的并应用其他方式.

But as I cannot modify server and both msxml3.dll and msxml6.dll are locked - I need to understand what is wrong and apply other way.

代码很简单，用于抓取soap web服务:

Code is simple as usual for grabbing soap web service:

Declare @Object as Int;
Declare @ResponseText as Varchar(8000);
Declare @ErrCode    Int;

Exec sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'post','http://example.com/Authentication.asmx','false'
Exec sp_OAMethod @Object ,'setRequestHeader'    ,NULL ,'Content-Type'   ,'text/xml; charset=utf-8'
Exec sp_OAMethod @Object ,'setRequestHeader'    ,NULL ,'SOAPAction' ,'"http://www.example.com/Login"'
Exec @ErrCode=sp_OAMethod @Object, 'send',null,'<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <soap:Body>
    <Login xmlns="http://www.example.com/">
      <databaseName>db1</databaseName>
      <userName>login</userName>
      <password>pass</password>
    </Login>
  </soap:Body>
</soap:Envelope>'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
Select @ErrCode,@ResponseText
Exec sp_OADestroy @Object  

我尝试了 MSXML2.XMLHTTP 和 MSXML2.ServerXMLHTTP(以及 .6.0 版本)对象.
错误 ID:-2147024809，备注发送"失败.参数不正确.

I tried both MSXML2.XMLHTTP and MSXML2.ServerXMLHTTP (as well as .6.0 versions) objects.
Error id: -2147024809, with remark 'send' failed. The parameter is incorrect.

当然Ole Automation Procedures是启用的.

推荐答案

我在整个周末偶然发现了这个令人讨厌的问题.我个人觉得替换 DLL"的解决方法很糟糕，所以我尽力想出一个更安全的解决方案......幸运的是，我实际上找到了其中的两个.

I stumbled upon this nasty issue for an entire weekend. I personally found horrible the "replace DLL" workaround, so I did my best to come out with a safer solution... Luckily enough, I actually found two of them.

解决方案 1

应用以下 MS HotFix，彻底解决问题:

Apply the following MS HotFix, which fixes the issue for good:

• https://support.microsoft.com/en-us/help/2968741/error-0x80070057-when-sql-server-communicates-to-a-web-server-using-st

(阅读帖子了解更多信息，并通过 MS 安全渠道通过电子邮件请求修补程序)

(read the post for further info and to request the hotfix via e-mail through MS secure channels)

解决方案 2

如果您不能应用 HotFix，您仍然可以通过在发出 SEND 命令时使用稍微不同的语法来完成工作.而不是这样:

If you can't apply the HotFix, you can still get the job done by using a slightly different syntax when issuing the SEND command. Instead of this:

Exec @ErrCode=sp_OAMethod @Object, 'send',null,'your-data';

这样做:

Exec @ErrCode=sp_OAMethod @Object, 'send("your-data")';

它适用于任何类型的 HTTP 请求数据:JSON、XML 甚至 application/x-www-form-urlencoded 用于标准POST 请求.缺点是这种语法非常难看……而且您必须以这种方式更改所有存储过程.

It works for any type of HTTP request data: JSON, XML and even application/x-www-form-urlencoded for standard POST request. The downside is that such syntax is quite ugly... and you have to change all your Stored Procedures that way.

有关该问题的其他信息，您还可以在我的博客上阅读这篇文章.

For additional info regarding the issue you can also read this post on my blog.

这篇关于上下文 sp_OAMethod 'send' 中的 msxml3.dll的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！