CALLING WEB-SERVICE FROM SP
----------------------------
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
else
select @obj
exec @hr = sp_OAMethod @obj, 'Open', NULL, 'GET', @sUrl, false
if @hr <0
begin
set @msg = 'sp_OAMethod Open failed'
goto eh
end
else
select @hr, @obj, @surl
exec @hr = sp_OAMethod @obj, 'send'
if @hr <0
begin
set @msg = 'sp_OAMethod Send failed'
goto eh
end
else
select @hr, @obj
exec @hr = sp_OAGetProperty @obj, 'status', @status OUT
if @hr <0
begin
set @msg = 'sp_OAMethod read status failed'
goto eh
end
else
select 'sp_OAGetProperty of '+ convert(varchar, @obj) +' executed with status='+ convert(varchar, @status) + ' '
/*
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
else
select @response
exec @hr = sp_OADestroy @obj
return
--Error handling code
eh:
exec @hr = sp_OADestroy @obj
Raiserror(@msg, 16, 1)
return
go
/*
then run this
declare @resp varchar(8000)
exec http_get 'http://localhost/WebServiceToBeCalledFromSP/HelloWorld.asmx?op=CallMe',@resp out
print @resp
*/
posted by Soumalyade @ 11:38 PM
