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
Copy link

Well,

How can I generate the @authHeader?

Thks

@mbgarcia
Copy link

Hi,

I use this method:

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

@MovGP0
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
Copy link

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

@Kyle-Lowe
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;

@afvianna
Copy link

afvianna commented Nov 6, 2020

How should I post data using body and GET method?

I am trying this code

EXEC @ret = sp_OAMethod @token, 'open', NULL, 'GET', @url, 'false';
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'wts-session', @apiSession;
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'wts-licencetype', @licencetype;
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', @contentType;
EXEC @ret = sp_OAMethod @token, 'send', NULL, @Body

@Astarotus-GP
Copy link

Hі!

Can you please help correctly compose multipart/form-data request?

Thks

@cissemy
Copy link

cissemy commented Jul 7, 2022

Hi
The post request does not work for sql server 2008.
Any reason ?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment