msxml3.dll in context sp_OAMethod 'send'

Question

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

The only ~real solution I found is:

I copied C:\Windows\System32\msxml3.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.

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.

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  

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.

Of course Ole Automation Procedures is enabled.

Answer 1

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.

Solution 1

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

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

Solution 2

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';

do this:

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

Answer 2

Specify MSXML2.ServerXMLHTTP.3.0 instead of 6.0.