How to fix MSXML2.XMLHTTP and MSXML2.ServerXMLHttp Error 0x80070057 - The Parameter is Incorrect

Ryan

7 years ago

UPDATE: if you're looking for a way to issue GET or POST calls to an external URL from a SQL Server Stored Procedure, read this post.

If you found this post it probably means that you are experiencing the following scenario:

A Windows 2012 or Windows 2016 Server machine with SQL Server 2008, SQL Server 2008R2, SQL Server 2012, SQL Server 2014, SQL Server 2016 and so on.
A Stored Procedure using a MSXML2 object that calls a Web Service (or any other external URL) with a POST request (see below).
An error 0x80070057 (or -2147024809) occurring as soon as you call the "SEND" method (see below), which translates (when inspected using sp_OAGetErrorInfo) as The parameter is incorrect.

Here's an example of a SP that might throw such error:

DECLARE @obj AS int;
DECLARE @result AS int;
DECLARE @response AS NVARCHAR(4000);
DECLARE @status AS int;
DECLARE @url AS varchar(2000) = 'http://www.anyurl.com';
DECLARE @parms AS varchar(3000) = 'p1=a&p2=b&p3=c';

EXEC @result = sp_OACreate 'MSXML2.ServerXMLHTTP', @obj OUT;
EXEC @result = sp_OAMethod @obj, 'open', NULL, 'POST', @url, false;
EXEC @result = sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded';
EXEC @result = sp_OAMethod @obj, 'send', NULL, @parms;

DECLARE @obj AS int;

DECLARE @result AS int;

DECLARE @response AS NVARCHAR(4000);

DECLARE @status AS int;

DECLARE @url AS varchar(2000) = 'http://www.anyurl.com';

DECLARE @parms AS varchar(3000) = 'p1=a&p2=b&p3=c';

EXEC @result = sp_OACreate 'MSXML2.ServerXMLHTTP', @obj OUT;

EXEC @result = sp_OAMethod @obj, 'open', NULL, 'POST', @url, false;

EXEC @result = sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded';

EXEC @result = sp_OAMethod @obj, 'send', NULL, @parms;

The last line is where the error occurs. The above example uses MSXML2.ServerXMLHTTP, but the same identical issue will also happen with any MSXML2 object available: MSXML2.XMLHTTP, MSXML2.ServerXMLHTTP.3.0, MSXML2.ServerXMLHTTP.6.0 and so on.

In case you recently moved your DB server machine from Windows 2008 Server (or earlier) to Windows 2012 Server (or newer) you might also experience the fact that the script used to flawlessly work with the old environment, leading you to the following question:

What happened?

That very same scenario occurred to me this weekend: I had just finishing upgrading an old Windows 2008 Server to a brand-new Windows 2012R2 one: while doing this, I chose to migrate the same identical SQL Server 2008 SP4 database - without upgrading it as well - to be 100% sure that I wouldn't hit any issue... Needless to say, I was wrong: a couple of SP containing a code similar to the above one suddendly stopped working.

I immediately tried to google-out the solution, yet I found nothing: the web was (and still is) full of MSXML2 connection examples, but every one of them is using the same identical logic I was using - which was also working, after all, until I chose to upgrade the server OS.

Luckily enough, I finally stumbled upon a workaround which - as horrible as it was - pointed me to the right direction.

The (ugly) workaround

In a Windows forum there was an user claiming to have fixed the issue by replacing the following Windows 2012 Server files with their Windows 2008 Server counterparts:

C:\Windows\System32\MSCML3.dll
C:\Windows\System32\MSCML6.dll

1 2	C:\Windows\System32\MSCML3.dll C:\Windows\System32\MSCML6.dll

Needless to say, the former one fixes MSXML2.ServerXMLHTTP.3.0 object, while the latter fixes the MSXML2.ServerXMLHTTP.6.0 one. I quickly tried to do that and (after a quick reboot) I found that it was indeed working! Regardless of that, I was far from satisfied - replacing system files can hardly be a viable solution, as it would most likely leave the server prone to future regression bugs after any upgrade, service pack, OS restore/migration and so on.

The (viable) solution

The good thing about the ugly workaround was that it led me into a much better solution: I started to think that such odd behaviour could be a strong sign that MS silently reimplemented the send method in a different way OR made some changes in the calling interface. I started digging into Google again and I was quickly rewarded with an official Microsoft code sample that confirmed that theory.

Here's the old MSXML2 send interface:

EXEC @result = sp_OAMethod @obj, 'send', NULL, 'p1=a&p2=b&p3=c';

1	EXEC @result = sp_OAMethod @obj, 'send', NULL, 'p1=a&p2=b&p3=c';

And here's the new one:

EXEC @result = sp_OAMethod @obj, 'send("p1=a&p2=b&p3=c")';

1	EXEC @result = sp_OAMethod @obj, 'send("p1=a&p2=b&p3=c")';

Once I discovered that, I just had to slightly rewrite my Stored Procedures in the following way:

DECLARE @obj AS int;
DECLARE @result AS int;
DECLARE @response AS NVARCHAR(4000);
DECLARE @status AS int;
DECLARE @url AS varchar(2000) = 'http://www.anyurl.com';
DECLARE @parms AS varchar(3000) = 'p1=a&p2=b&p3=c';

EXEC @result = sp_OACreate 'MSXML2.ServerXMLHTTP', @obj OUT;
EXEC @result = sp_OAMethod @obj, 'open', NULL, 'POST', @url, false;
EXEC @result = sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded';

-- DISABLED - Not working in Windows 2012 Server
-- EXEC @result = sp_OAMethod @obj, 'send', NULL, @parms; 

DECLARE @send NVARCHAR(4000) = 'send("' + REPLACE(@parms, '"', '''') + '")';
EXEC @result = sp_OAMethod @obj, @send;

DECLARE @obj AS int;

DECLARE @result AS int;

DECLARE @response AS NVARCHAR(4000);

DECLARE @status AS int;

DECLARE @url AS varchar(2000) = 'http://www.anyurl.com';

DECLARE @parms AS varchar(3000) = 'p1=a&p2=b&p3=c';

EXEC @result = sp_OACreate 'MSXML2.ServerXMLHTTP', @obj OUT;

EXEC @result = sp_OAMethod @obj, 'open', NULL, 'POST', @url, false;

EXEC @result = sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded';

-- DISABLED - Not working in Windows 2012 Server

-- EXEC @result = sp_OAMethod @obj, 'send', NULL, @parms;

DECLARE @send NVARCHAR(4000) = 'send("' + REPLACE(@parms, '"', '''') + '")';

EXEC @result = sp_OAMethod @obj, @send;

That fixed everything without having to replace system DLLs. However, I was quite lucky because I only had a couple SP to actually "patch": if you have a lot more, and you're in a hurry, you could find the (ugly) workaround to be better, at least until you have time to come up with a better strategy.

The (definitive) fix

After further investigations, I eventually found out that Microsoft acknowledged the issue and even came out with an HotFix to effectively address it. Here's a link to the official MS post, which includes a form to request the HotFix via e-mail through secure channels.

Altough I was very proud of my previous findings I definitely have to say that, at least in my humble opinion, the official MS HotFix is the recommended way to address the issue. It's also worth noting that, once you apply the patch, both the SEND method calling interfaces will work, thus saving the DBA from having to do a lot of work!

That's it for now: I sincerely hope that my findings will help other developers & DBAs struggling with this nasty issue!

Print Friendly & PDF Download