Using MSXML2 from a SP on SQL 2014

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

, +

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:

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

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!

Posted 4-Aug-14 18:10pm

_Maxxx_

Updated 7-Jul-17 15:03pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Maxxx_ · Answer 1 · 2014-08-05T20:31:00

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!

Zsolt v · Answer 2 · 2015-03-13T14:02:00

Solution 2

use:

SQL

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

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

Posted 13-Mar-15 14:02pm

Zsolt v

Comments

CHill60 14-Mar-15 10:53am

Problem was resolved over 6 months ago - there was nothing wrong with the code it was the wrong DLL.