HTTP Post using T-SQL

I thought to re-post this useful query which helps to do HTTP POST using T-SQL

declare @iobject int,

@ihr int,

@data nvarchar(4000) ,

@smtpWebService varchar(200),

@retVal varchar(8000),

@src varchar(1000),

@desc varchar(1000)

exec @ihr = sp_oaCreate 'Msxml2.ServerXMLHTTP.3.0', @iobject OUTPUT

if (@ihr <> 0)

begin

exec sp_displayoaerrorinfo @iobject, @ihr return

end

set @data = 'boo=yeah'

set @smtpWebService = 'http://www.google.com'

exec @ihr = sp_OAMethod @iobject, 'Open', null, 'POST',@smtpWebService, 0

exec @ihr = sp_OAMethod @iobject, 'setRequestHeader', null, 'Content-Type', 'application/x-www-form-urlencoded'

exec @ihr = sp_OAMethod @iobject, 'send', null, @data

if @ihr <> 0

begin

exec sp_OAGetErrorInfo @iobject, @src OUT, @desc OUT

select hr = convert(varbinary(4),@ihr), Source = @src, Description = @desc

--raiserror('Error Creating COM Component 0x%x, %s, %s',16,1, @ihr, @src, @desc) return

print @src

print @desc

end

exec @ihr = sp_OAGetProperty @iObject, 'responseText', @retVal OUT

print @retVal

-- Destroy the object

exec @ihr = sp_OADestroy @iobject

Original post link:

http://techknowledges.blogspot.com/2007/10/t-sql-http-post-string.html

sp_displayoaerrorinfo 

CREATE PROCEDURE sp_hexadecimal

    @binvalue varbinary(255),

    @hexvalue varchar(255) OUTPUT

AS

DECLARE @charvalue varchar(255)

DECLARE @i int

DECLARE @length int

DECLARE @hexstring char(16)

SELECT @charvalue = '0x'

SELECT @i = 1

SELECT @length = DATALENGTH(@binvalue)

SELECT @hexstring = '0123456789abcdef'

WHILE (@i <= @length)

BEGIN

DECLARE @tempint int

DECLARE @firstint int

DECLARE @secondint int

SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))

SELECT @firstint = FLOOR(@tempint/16)

SELECT @secondint = @tempint - (@firstint*16)

SELECT @charvalue = @charvalue +

SUBSTRING(@hexstring, @firstint+1, 1) +

SUBSTRING(@hexstring, @secondint+1, 1)

SELECT @i = @i + 1

END

SELECT @hexvalue = @charvalue

go

CREATE PROCEDURE sp_displayoaerrorinfo

    @object int,

    @hresult int

AS

DECLARE @output varchar(255)

DECLARE @hrhex char(10)

DECLARE @hr int

DECLARE @source varchar(255)

DECLARE @description varchar(255)

PRINT 'OLE Automation Error Information'

EXEC sp_hexadecimal @hresult, @hrhex OUT

SELECT @output = '  HRESULT: ' + @hrhex

PRINT @output

EXEC @hr = sp_OAGetErrorInfo @object, @source OUT, @description OUT

IF @hr = 0

BEGIN

SELECT @output = '  Source: ' + @source

PRINT @output

SELECT @output = '  Description: ' + @description

PRINT @output

END

ELSE

BEGIN

    PRINT ' sp_OAGetErrorInfo failed.'

    RETURN

END

Cheers!

Uma