Technical Article

Make HTTP posts / request from within SQL

,

Use this script to make HTTP posts or requests from withing SQL. It uses the XML HTTP COM object.

DECLARE 
-- @vPointer is a pointer to the object and / or any methods or property
@vPointer INT, 
@vResponseText VARCHAR(8000),
@vStatus INT,
@vStatusText VARCHAR(200),
@vSource VARCHAR(255),
@vDescription VARCHAR(500)
-- Instantiate the object (Gonna use XMLHTTP Version2. This is avaliabe on W2K+)
EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @vPointer OUTPUT
--Check to see if errors where created.
EXEC sp_OAGetErrorInfo @vPointer, @vSource OUT, @vDescription OUT
IF @vSource Is Not Null
Begin
Select 'Error While Creating HTTP Object' AS SOMFINGWONG, @vSource as Source, @vDescription as [Description]
--Return --Add if it's a proc.
End
-- Open a connection to the URL. This does not send anything yet! ('GET' | 'POST') ('Any valid URL')
EXEC sp_OAMethod @vPointer, 'OPEN', NULL, 'GET', '<http://localhost/Fiber/clients/'>
EXEC sp_OAGetErrorInfo @vPointer, @vSource OUT, @vDescription OUT
IF @vSource Is Not Null
Begin
Select 'Error While opening connection', @vSource as Source, @vDescription as [Description]
--Return --Add if it's a proc.
End
-- Send the request.
EXEC sp_OAMethod @vPointer, 'send'
EXEC sp_OAGetErrorInfo @vPointer, @vSource OUT, @vDescription OUT
IF @vSource Is Not Null
Begin
Select 'Error While sending data', @vSource as Source, @vDescription as [Description]
--Return --Add if it's a proc.
End
-- Send the request.
-- If it got this far, there _should_ not be any more errors. :) You may add the error code here to if you like.
EXEC sp_OAMethod @vPointer, 'responseText', @vResponseText OUTPUT
EXEC sp_OAMethod @vPointer, 'Status', @vStatus OUTPUT
EXEC sp_OAMethod @vPointer, 'StatusText', @vStatusText OUTPUT
EXEC sp_OADestroy @vPointer
Select @vStatus, @vStatusText, SubString(@vResponseText, 1, 10) + ' Result Trimmed'

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating