Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Demonstrates how to send an HTTP request with SQL Server using OLE Automation.
EXEC sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
DECLARE @authHeader NVARCHAR(64);
DECLARE @contentType NVARCHAR(64);
DECLARE @postData NVARCHAR(2000);
DECLARE @responseText NVARCHAR(2000);
DECLARE @responseXML NVARCHAR(2000);
DECLARE @ret INT;
DECLARE @status NVARCHAR(32);
DECLARE @statusText NVARCHAR(32);
DECLARE @token INT;
DECLARE @url NVARCHAR(256);
SET @authHeader = 'BASIC 0123456789ABCDEF0123456789ABCDEF';
SET @contentType = 'application/x-www-form-urlencoded';
SET @postData = 'value1=Hello&value2=World';
SET @url = 'https://en43ylz3txlaz.x.pipedream.net';
-- Open the connection.
EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT;
IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);
-- Send the request.
EXEC @ret = sp_OAMethod @token, 'open', NULL, 'POST', @url, 'false';
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Authentication', @authHeader;
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', @contentType;
EXEC @ret = sp_OAMethod @token, 'send', NULL, @postData;
-- Handle the response.
EXEC @ret = sp_OAGetProperty @token, 'status', @status OUT;
EXEC @ret = sp_OAGetProperty @token, 'statusText', @statusText OUT;
EXEC @ret = sp_OAGetProperty @token, 'responseText', @responseText OUT;
-- Show the response.
PRINT 'Status: ' + @status + ' (' + @statusText + ')';
PRINT 'Response text: ' + @responseText;
-- Close the connection.
EXEC @ret = sp_OADestroy @token;
IF @ret <> 0 RAISERROR('Unable to close HTTP connection.', 10, 1);
@mbgarcia

This comment has been minimized.

Copy link

mbgarcia commented May 23, 2018

Well,

How can I generate the @authHeader?

Thks

@mbgarcia

This comment has been minimized.

Copy link

mbgarcia commented May 24, 2018

Hi,

I use this method:

SELECT CAST('sometext' as varbinary(max)) FOR XML PATH(''), BINARY BASE64

@MovGP0

This comment has been minimized.

Copy link

MovGP0 commented Aug 28, 2018

there should be an example for reading response headers. ie.

DECLARE @header NVARCHAR; 
EXEC @ret = sp_OAMethod @token, 'getResponseHeader', @header OUTPUT, 'Headername'; 
@alirezaimi

This comment has been minimized.

Copy link

alirezaimi commented Sep 23, 2018

not work for me ! why !? how can i debug it ?

@Kyle-Lowe

This comment has been minimized.

Copy link

Kyle-Lowe commented Jan 28, 2019

Not sure if this is still an issue, but

DECLARE @key NVARCHAR(100) = [GUID/KEY]; DECLARE @authHeader = ['Bearer/Basic'] + @key; EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Authorization', @authHeader;

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.