Generate Http Request through SQLserver 2008 R2 Proc

This Article has used to execute/visit the http request via Sqlserver 2008 R2 stored proc.Sometimes we need to call some external WS service/Http Url inside the backend objects.I think this will helpfull to resolve this kind of queries.

Before the creation we nedd to Grant permision to following system object under desired sqlserver user.

sp_OASetProperty
sp_OAMethod
sp_OAGetErrorInfo
sp_OADestroy
sp_OAStop
sp_OACreate
sp_OAGetProperty

Sqlserver Stored Procedure

Create procedure [dbo].[HTTP_DB_ALERT]( @sUrl varchar(2000), @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.3.0
failed', 16,1) return end

exec @hr = sp_OAMethod @obj, 'open', NULL, 'POST', @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
Raiserror(@msg, 16, 1)
return