Using MSXML2.ServerXMLHTTP within Stored Procedure To Grab Source of HTML Page and save to table

Question

Post reply

Using MSXML2.ServerXMLHTTP within Stored Procedure To Grab Source of HTML Page and save to table

smhunt

Mr or Mrs. 500

Points: 577
More actions
September 14, 2004 at 10:06 am

#83977

I'm trying to use 'MSXML2.ServerXMLHTTP' though extended store procedures to retrieve the html source of parsed asp pages to be stored into a database for later use in a mail que system
When I execute the following code in Query Analyzer the @vResponseText variable is returned NULL
DECLARE
@vPointer INT,
@vResponseText VARCHAR(8000),
@vStatus INT,
@vStatusText VARCHAR(200)
EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @vPointer OUTPUT
EXEC sp_OAMethod @vPointer, 'open', NULL, 'GET', 'http://tlcpet.com/default.asp'
EXEC sp_OAMethod @vPointer, 'send'
EXEC sp_OAMethod @vPointer, 'responseText', @vResponseText OUTPUT
EXEC sp_OAMethod @vPointer, 'Status', @vStatus OUTPUT
EXEC sp_OAMethod @vPointer, 'StatusText', @vStatusText OUTPUT
EXEC sp_OADestroy @vPointer
Select @vStatus, @vStatusText, @vResponseText
However, when i execute the following code (omiiting the output variable and selecting 'responseText' directly) I get the source of the page as intended (Not NULL)
DECLARE
@vPointer INT,
@vResponseText VARCHAR(8000),
@vStatus INT,
@vStatusText VARCHAR(200)
EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @vPointer OUTPUT
EXEC sp_OAMethod @vPointer, 'open', NULL, 'GET', 'http://tlcpet.com/default.asp'
EXEC sp_OAMethod @vPointer, 'send'
EXEC sp_OAMethod @vPointer, 'responseText' --, @vResponseText OUTPUT
EXEC sp_OAMethod @vPointer, 'Status', @vStatus OUTPUT
EXEC sp_OAMethod @vPointer, 'StatusText', @vStatusText OUTPUT
EXEC sp_OADestroy @vPointer
Select @vStatus, @vStatusText, @vResponseText
I need to be able to 'responseText' into the OUTPUT variable @vResponseText so that I can then insert it into a table
Can someone see where I'm going wrong? XMLHttp IS properly installed and functioning (the latter example and many other applications on this server use the object succesfully)

Thank in advance, I'm stumped on this

Viewing 9 posts - 1 through 8 (of 8 total)

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

Brian Hinken SSC Journeyman Points: 86 More actions · Answer 1

EXEC sp_OAGetProperty @vPointer, 'responseText', @vResponseText OUTPUT

Do the same for 'Status' and 'StatusText'

smhunt Mr or Mrs. 500 Points: 577 More actions · Answer 2

Hi there thanks for the suggestion, but no success....

Have you tried the code you suggested in query analyzer on you machine and got it to work?

In my orginal example Do the same for 'Status' and 'StatusText' work properly using EXEC sp_OAMethod @vPointer, 'Status', @vStatus OUTPUT

Its only the response Text line that doesnt work

I get a NULL value returned for responseText both ways (using sp_OAMethod and sp_OAGetProperty)

intrestingly I also sucessfully get a correct value 'Status' and 'StatusText' using using sp_OAMethod or sp_OAGetProperty

any further ideas on this one?

ignas SSC Journeyman Points: 83 More actions · Answer 3

Had the same problem with the following code >>

Funny thing is that if I use @response varchar(MAX) instead of @response varchar(8000) it will fail and tohing will be returned

Suggestion to play with a variable space in your own code there might be some lmitations ...

this one works for me

CREATE procedure HTTP_POST( @sUrl varchar(200), @response varchar(8000) out, @error varchar(100) out)

As

Declare

@obj int

,@hr int

,@status int

,@msg varchar(255)

exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT

-- exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT

if @hr <> 0 begin Raiserror('sp_OACreate MSXML2.ServerXMLHttp.3.0

failed', 16,1) return end

exec @hr = sp_OAMethod @obj, 'open', NULL, 'GET', @sUrl, false

if @hr <>0 begin set @msg = 'sp_OAMethod Open failed' goto eh end

exec @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type',

'application/x-www-form-urlencoded'

if @hr <>0 begin set @msg = 'sp_OAMethod setRequestHeader failed' goto

eh end

exec @hr = sp_OAMethod @obj, send, NULL, ''

if @hr <>0 begin set @msg = 'sp_OAMethod Send failed' goto eh end

exec @hr = sp_OAGetProperty @obj, 'status', @status OUT

if @hr <>0 begin set @msg = 'sp_OAMethod read status failed' goto

eh

end

if @status <> 200 begin set @msg = 'sp_OAMethod http status ' +

str(@status) goto eh end

exec @hr = sp_OAGetProperty @obj, 'responseText', @response OUT

if @hr <>0 begin set @msg = 'sp_OAMethod read response failed' goto

eh end

exec @hr = sp_OADestroy @obj

return

eh:

exec @hr = sp_OADestroy @obj

set @error = @msg

return

GO

jonathan.smith SSC Journeyman Points: 85 More actions · Answer 4

jonathan.smith

SSC Journeyman

Points: 85

February 20, 2015 at 7:32 am

#1778948

Many Thanks, this solution saved my bacon!!!

sandeep1k.mishra Valued Member Points: 59 More actions · Answer 5

sandeep1k.mishra

Valued Member

Points: 59

April 28, 2015 at 2:13 am

#1793873

Thanks it's working fine

:-):-):-):-):-):-)

francesco.mantovani SSC Eights! Points: 840 More actions · Answer 6

14 years have passed, it's time to update the reply.
Also, this query can:

1) catch the error
2) put the REST call response into a string
3) parse the JSON and put it into a table
4) fix the problem with the NVARCHAR(MAX) and it's now storing till 2GB of size

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', 'http://overpass-api.de/api/interpreter?data=[out:json];area[name=%22Auckland%22]-%3E.a;(node(area.a)[amenity=cinema];way(area.a)[amenity=cinema];rel(area.a)[amenity=cinema];);out;', --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 -- Parse the JSON string SELECT * FROM OPENJSON((select * from @json), N'$.elements') WITH ( [type] nvarchar(max) N'$.type' , [id] nvarchar(max) N'$.id', [lat] nvarchar(max) N'$.lat', [lon] nvarchar(max) N'$.lon', [amenity] nvarchar(max) N'$.tags.amenity', [name] nvarchar(max) N'$.tags.name' ) EXEC sp_OADestroy @Object

mark.derail 58050 Grasshopper Points: 12 More actions · Answer 7

TYVM Francesco, I was trying to get around the problem of using an nvarchar(max) in the output for 'responseText', which doesn't work.

What works is nvarchar(4000) or varchar(8000), for the OUTPUT for 'responseText'.

Your solution is perfect! Thanks for updating.

This reply was modified 4 years, 5 months ago by mark.derail 58050.

sreenatht Newbie Points: 1 More actions · Answer 8

Hi Mark,

I am new to SQLServer Coding , Just wanted a confirmation on msxml.serverxmlhttp call.

I was trying to make a rest api call using Exec @hr=sp_OACreate 'MSXML2.ServerXMLHTTP', but i found that msxml2 is 2.xx version which is not supported by Microsoft anymore and we have to use the msxml version 3.0 or 6.0.

I have tried using msxml3 or msxml6 but they have failed.

However after checking, i found that we use the command for msxml 6.0 version as below:

Exec @hr=sp_OACreate 'MSXML2.ServerXMLHTTP.6.0',

Can you please confirm if in the above call, it is using the msxml version 6.0 ?