HTTP Request from a SQL Server 2000 stored procedure

[Eugen Tanase]

Hello,

Is there any possibility to have a HTTP request from a SQL
Server 2000 stored procedure and receive as response an
XML file ?

Thank you in advance

[no potted meat@hotmail.com David Browne]

"Eugen Tanase" <eugen....@sympatico.ca> wrote in message
news:0a5201c32a50$9357c920$a401...@phx.gbl...

Here's an example. You will need Microsoft XML parser version 3 or higher
on your SQLServer.
It's installed with IE so it's likely that it's already there.

David

create procedure http_get( @sUrl varchar(200), @response varchar(8000) out)
As

Declare
@obj int
,@hr int
,@status int
,@msg varchar(255)

exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
if @hr < 0 begin Raiserror('sp_OACreate MSXML2.ServerXMLHttp 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, 'send'
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
Raiserror(@msg, 16, 1)
return

go

then run this, to make an HTTP request

declare @resp varchar(8000)
exec http_get
'http://[webserver]/[virtualDirectory]/doSomething.asp?whatToDo=thing,@resp
out
print @resp

[Eugen Tanase]

Thank you very much David. I will test it tomorow.
Thank you again.