在SQL 2014上使用SP中的MSXML2 [英] Using MSXML2 from a SP on SQL 2014

问题描述

我有一个使用MSXML2调用Web服务的SP。

它使用SQL Server 2008R2在我的本地计算机上工作

它在我的服务器上不起作用，使用SQL Server 2014.



从命令行从Javascript程序运行相同的MSXML2，适用于本地计算机和服务器。



SQL Server服务在两台机器上的Admin用户下运行。



这是SQL：

I have a SP using MSXML2 to call a Web Service.
It is working on my local machine using SQL Server 2008R2
It is not working on my server, using SQL Server 2014.

Running the same MSXML2 from a Javascript program from the command line, works on both local machine and server.

The SQL Server service is running under an Admin user on both machines.

Here's the SQL:

Declare @Object as Int;
Declare @ResponseText as Varchar(8000);
Declare @Status as int;
Declare @Body as varchar(8000) =
'{"someParameterName": ["AAAAAAAA" ],"Message": "Here is a message I am sending","securityId": "DA6AC379-703F-4BCC-9B0B-8DBAF87EB5C6","type": "send_message"}'

Declare @BodyLength int = LEN(@Body)
Exec sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'setProxy', '2', 'http://localhost:8888',''
Exec sp_OAMethod @Object, 'open', NULL, 'post',
    'https://api.theUrl.com/external/TheMessageApi',
    'false'

Exec sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'
Exec sp_OAMethod @Object, 'setRequestHeader', null, 'Content-LEngth', @BodyLength

Exec sp_OAMethod @Object, 'send', null , @Body


Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
Exec sp_OAMethod @Object, 'status', @status OUTPUT
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

Select @ResponseText as Response, @Status as Status

Exec sp_OADestroy @Object

在我的本地机器上，我看到响应文本和状态为200

在服务器上我看到Null，Null



我很欣赏这有点具体 - 但我不知道还有什么可看的！



我试过安装Fiddler - 但即使通话有效，也无法吸引流量！



感激不尽的任何建议！

On my local machine I see the response text and status of 200
On the server I see Null, Null

I appreciate this is a bit specific - but I'm at a loss what else to look at!

I tried installing Fiddler - but that doesn't catch the traffic even when the call works!

Any advice gratefully received!

推荐答案

好吧，看起来我必须自己回答。



事实证明，Windows 7上的MSXML6.dll与MSXML6不同。 Windows Server 2012上的dll



我不知道有什么不同，但2012年版本的'send'方法失败，错误

Msxml6.dll 0x80070057



这意味着'参数不正确'



T他的问题与MSCML3.dll一样！



所以 - 我复制了来自Windows \ System32的两个DLL（从我的工作站到服务器）给了它一个快速重启和BINGO！



经验教训 - 我应该查看每次调用DLL的返回代码 - 我会尽快找到问题！





但现在我可以从我的SQL Server触发器调用Web服务，所以我很开心！

Well, OK , looks like I will have to answer it myself.

It turns out that the MSXML6.dll on Windows 7 is different to the MSXML6.dll on Windows Server 2012

I don't know what the difference is, but the 'send' method fails on the 2012 version with an error
Msxml6.dll 0x80070057

which means 'The parameter is incorrect'

The problem is the same with MSCML3.dll!

So - I copied over the two DLLs from Windows\System32 (from my workstation to the server) gave it a quick reboot and BINGO!

Lesson learned - I should have looked at the return codes from each call to the DLL - I would have tracked down the issue sooner!


But now I can call a web service from my SQL Server trigger, so I am a happy chappy!

使用：

use:

DECLARE @request varchar(max)
SET @request = 'send("' + REPLACE(@Body, '"', '''') + '")'
Exec sp_OAMethod @Object, @request

INSERT INTO @result
 EXEC sp_OAGetProperty @Object, 'responseText'

这篇关于在SQL 2014上使用SP中的MSXML2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！