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
