Skip to content

Instantly share code, notes, and snippets.

@MarioBinder
Forked from JahsonKim/enableOleAutomation.sql
Last active February 24, 2022 12:14
Show Gist options
  • Save MarioBinder/1f499ade78dc17871eb3ea133bdd7ffb to your computer and use it in GitHub Desktop.
Save MarioBinder/1f499ade78dc17871eb3ea133bdd7ffb to your computer and use it in GitHub Desktop.
How to send http POST request from sql server stored procesdure.
--This query enables ole automation procedures. set 0 to disable
exec master.dbo.sp_configure 'Ole Automation Procedures', 1
RECONFIGURE
--OLE automation disabled the following error is thrown.
--SQL Server blocked access to procedure 'sys.sp_OACreate' of component
--'Ole Automation Procedures' because this component is turned off as part
--of the security configuration for this server.
--A system administrator can enable the use of 'Ole Automation Procedures'
--by using sp_configure. For more information about enabling 'Ole Automation Procedures',
--search for 'Ole Automation Procedures' in SQL Server Books Online.
--Running the above query may result to this erro
--The configuration option 'Ole Automation Procedures' does not exist, or it may be an advanced option.
--To fix this error run the query below and the enable OLE automation
exec master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
--create/alter a stored proceudre accordingly
create procedure webRequest
as
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);
DECLARE @Authorization NVARCHAR(200);
--set your post params
SET @authHeader = 'BASIC 0123456789ABCDEF0123456789ABCDEF';
--SET @contentType = 'application/x-www-form-urlencoded';
SET @contentType = 'application/json';
--SET @postData = 'KeyValue1=value1&KeyValue2=value2'
SET @postData = '{ "Source": "DATABASE", "CreatedDate": "2022-02-24", "Test": "TEST" }'
SET @url = 'set your webhook url end point here'
-- 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';
--set a custom header Authorization is the header key and VALUE is the value in the header
--EXEC sp_OAMethod @token, 'SetRequestHeader', NULL, 'Authorization', 'VALUE'
--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);
go
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment