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

Generate a custom error message in Execute SQL Task in SSIS

Generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct.

declare @position varchar(20)

set @position = ?

if (@position like 'eng%' or @position like 'man%')

begin

delete from dbo.employee where position = @position;

end

else 

begin

raiserror ('position is an unidentified type',10,1)

end

go

Cheers!

Uma

T-SQL script - Delete files using file name pattern Ole Automation Procedures

Use the Ole Automation Procedures option to specify whether OLE Automation objects can be instantiated within Transact-SQL batches. This option can also be configured using the Policy-Based Management or the sp_configure stored procedure. Ole Automation Procedures is an alternate approach for xp_cmdshell to handle file system.

The following T-SQL script shows that how to delete file without xp_cmdshell 

IF OBJECT_ID('dbo.sp_Clean_Files') IS NOT NULL

DROP PROC dbo.sp_Clean_Files

GO

CREATE PROCEDURE dbo.sp_Clean_Files

      @Temp_File_Path varchar(300) = 'C:\temp', 

-- Temp folder path as input parameter

      @Temp_File_Prefix varchar(100) = '0101', 

-- Temp file prefix as input parameter

      @Temp_File_Extension varchar(10) = 'TMP' 

-- Temp file extension as input parameter

AS

BEGIN

      SET NOCOUNT ON;

     

      EXEC sp_configure 'show advanced options', 1;

      RECONFIGURE;

      EXEC sp_configure 'Ole Automation Procedures', 1;

      RECONFIGURE;

     

            DECLARE @ResultOP int

            DECLARE @OLE_Obj  int

            DECLARE @TempFullPath varchar(300)

            SET @TempFullPath = @Temp_File_Path +'\'+@Temp_File_Prefix+'*.'+@Temp_File_Extension -- define temp folder full path

            EXEC @ResultOP = sp_OACreate 'Scripting.FileSystemObject', @OLE_Obj OUTPUT

            EXEC @ResultOP = sp_OAMethod @OLE_Obj, 'DeleteFile', NULL, @TempFullPath

            EXEC @ResultOP = sp_OADestroy @OLE_Obj

      EXEC sp_configure 'Ole Automation Procedures', 0;

      RECONFIGURE;     

      EXEC sp_configure 'show advanced options', 0;

      RECONFIGURE;

END

GO

Cheers!

Uma