Use this script to make HTTP posts or requests from withing SQL. It uses the XML HTTP COM object.
2007-10-02 (first published: 2002-06-20)
15,452 reads
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'