Skip to content

Instantly share code, notes, and snippets.

@jarrettmeyer
Last active July 23, 2024 09:16
Show Gist options
  • Save jarrettmeyer/5990daf0db3b1f4fd759df6ed4099685 to your computer and use it in GitHub Desktop.
Save jarrettmeyer/5990daf0db3b1f4fd759df6ed4099685 to your computer and use it in GitHub Desktop.
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);
@Tassos12
Copy link

How can I get access token from AccessTokenURL using ClientID and ClientSecret and then pass it in EXEC sp_OAMethod @object, 'SetRequestHeader', NULL, 'Authorization', @MyToken ?

example:
SET @dataurl = 'https://xxx.xxx/data'
SET @AccessTokenURL = 'https://xxx.xxx/token'
SET @ClientID = 'myClientID'
SET @ClientSecret = 'myClientSecret'

Exec sp_OACreate 'WinHttp.WinHttpRequest.5.1', @object OUT;
Exec sp_OAMethod @object, 'open', NULL, 'get',@dataurl ,'False'

--------I think here I need to do a sp_OAMethod with post to get the token. Something like:
EXEC sp_OAMethod @object, 'Open', NULL, 'POST', @AccessTokenURL, 'false', @ClientID, @ClientSecret, @MyToken OUT

---- and then pass it:
EXEC sp_OAMethod @object, 'SetRequestHeader', NULL, 'Authorization', @MyToken

Exec sp_OAMethod @object, 'send'
Exec sp_OAMethod @object, 'responseText', @responseText OUTPUT

Thanks

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