Msxml3.dll in the context of sp_OAMethod 'send'
Working code from Win2003 + SQL Server 2005 does not work under Win2012 + SQL Server 2012 sp1.
The only ~ real solution I found is:
I copied C: \ Windows \ System32 \ msxml3.dll from server 2008 to the same dir on server 2012. Issue on server 2012 resolved, sending with POST and GET works fine.
But since I cannot change the server and both msxml3.dll and msxml6.dll are locked - I need to figure out what is wrong and apply another way.
The code is simple as usual to grab the soap webservice:
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
I tried both MSXML2.XMLHTTP and MSXML2.ServerXMLHTTP objects (as well as version 6.0).
Error ID: -2147024809, with "send" error. The parameter is incorrect
...
Of course Ole Automation Procedures
included.
source to share
I stumbled upon this annoying problem for a whole weekend. I personally found the "replace DLL" solution terrible, so I did my best to come up with a safer solution ... Luckily, I did find two of them.
Solution 1
Apply the following MS HotFix which fixes the problem permanently:
(read the post for more information and request a fix via email via MS secure channels)
Solution 2
If you cannot apply HotFix, you can complete the task using a slightly different syntax when issuing the SEND command. Instead of this:
Exec @ErrCode=sp_OAMethod @Object, 'send',null,'your-data';
do the following:
Exec @ErrCode=sp_OAMethod @Object, 'send("your-data")';
It works for any type of HTTP request data: JSON, XML, and even application / x-www-form-urlencoded for a standard POST request. The downside is that this syntax is pretty ugly ... and you must change all your stored procedures that way.
For more information on this issue, you can also read this post .
source to share