MSXML2.XMLHTTP

Question

MSXML2.XMLHTTP

brucehechler-976230

SSC Veteran

Points: 235
More actions
December 30, 2008 at 7:07 am

#99391

Using sp_OACreate(MSXML2.XMLHTTP)
Object creates. Open("POST"....) works. Send() never returns webpage.
I've tried GET and POST to various websites. No answer ever returns.
-->Here's the code that "should" work...
declare @inp char(80),@out varchar(300)
set nocount on
set @inp = 'some input request string '
Declare @userid varchar(15),@Password varchar(15)
set @userid = 'some userid'
set @Password = 'some password'
set @out = '???'
declare @Object int,@hr int,@URL varchar(80),@i int
set @URL = 'http://www.microsoft.com'
declare @NULL Binary
set @NULL = null
exec @hr= sp_OACreate 'MSXML2.XMLHTTP.6.0', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
RETURN
END
exec sp_OAMethod @object,'open',@hr out,'GET',@url,FALSE,@Userid,@Password
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
RETURN
END
EXEC sp_OAMethod @object, 'setRequestHeader',@hr out,'Content-Length','50'
if @hr != 0
BEGIN
EXEC sp_OAGetErrorInfo @object
Return
end
EXEC sp_OAMethod @object,'setRequestHeader',@hr out,'Content-type','application/x-www-form-urlencoded'
if @hr != 0
BEGIN
EXEC sp_OAGetErrorInfo @object
Return
end
EXEC sp_OAMethod @object,'send',@hr out,@inp
if @hr != 0
BEGIN
EXEC sp_OAGetErrorInfo @object
Return
end
set @i = 300
EXEC sp_OAGetProperty @object,'readystatechange',@hr OUT
-- Wait for response up to 30 seconds
while @hr != 4 and @i > 0
begin
EXEC sp_OAGetProperty @object,'readystatechange',@hr OUT
set @i = @i - 1
end
EXEC @hr= sp_OAGetProperty @object,'responseText',@out out
if @hr = 0 select @out
--if @hr = 0 EXEC @hr= sp_OAGetProperty @object,'responseText',@out OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
select 'Get Property',@hr
END
exec sp_OAdestroy @object

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sandeep Kataria Valued Member Points: 56 More actions · Answer 1

Can someone help.... i am getting a null response. if you pass the actual URL u do get a JSON back. Waht am i doing so wrong.....the HTTP gods are going against me!

Declare @Object as Int;

Declare @ResponseText as Varchar(8000);

Declare @Body as varchar(8000) =

'{

"Subsystem": 1,

"Exception": "",

"Message": "I have done what you asked",

"Time": "2014-06-09T11:16:35",

"Attribute": { "Number of attempts": "0" }

}'

Exec sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT;

EXEC sp_OAMethod @Object, 'open', NULL, 'get','https://eutils.ncbi.nlm.nih.gov/entrez/eutils/efetch.fcgi?db=pubmed&id=32277993', 'false'

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

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

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

Select @ResponseText

Exec sp_OADestroy @Object

Kind Regards,

Sandeep Kataria

kaj SSCommitted Points: 1605 More actions · Answer 2

Is the SQL server you're using allowed a direct outbound connection to the internet?

If you're using a proxy, then this must be set up in the internet settings on the server. Bear in mind that this is set up per user.

AFAIK, MSXML2.XMLHTTP doesn't allow you to specify proxy settings. It will always use the user's internet settings.

kaj SSCommitted Points: 1605 More actions · Answer 3

Okay, I was wrong. It does seem to support proxy setup:

https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms760236(v=vs.85)

https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms763680(v=vs.85)

So you might want to fool around with those parameters, if you need to utilize a proxy in order to get to the internet from the SQL server.

Sandeep Kataria Valued Member Points: 56 More actions · Answer 4

Hello kaj

Appreciate your response and pointing me to resources. The below code worked for me. apparently the declaration of version mattered. i commented out errors trap but they can easily be put back in to caught in the procedure.

Declare @Object as Int;

DECLARE @hr int

Declare @json as table(Json_Table nvarchar(max))

Exec @hr=sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @Object OUT;

IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object

Exec @hr=sp_OAMethod @Object, 'open', NULL, 'get',

'https://eutils.ncbi.nlm.nih.gov/entrez/eutils/efetch.fcgi?db=pubmed&id=32277993', --Your Web Service Url (invoked)

'false'

--IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object

Exec @hr=sp_OAMethod @Object, 'send'

--IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object

Exec @hr=sp_OAMethod @Object, 'responseText', @json OUTPUT

--IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object

INSERT into @json (Json_Table) exec sp_OAGetProperty @Object, 'responseText'

-- select the JSON string

select * from @json

EXEC sp_OADestroy @Object

Kind Regards,

Sandeep Kataria