How to send http POST request from sql server stored procedure. Important! For some reason sp_OAGetProperty is returning null. Am not sure why and if anyone has an idea can update the gists. In case you need to try another option SQL CLR would help. Check here https://gist.github.com/JahsonKim/05e6af7744f2d7ef814e5ed331419db5

enableOleAutomation.sql

--This query enables ole automation procedures.  set 0 to disable
exec master.dbo.sp_configure 'Ole Automation Procedures', 1
RECONFIGURE

--OLE utomation 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

webRequest.sql

--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 @postData = 'KeyValue1=value1&KeyValue2=value2'
SET @url = 'set your 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

Not working with SQLServer 2008. Could you help update this?

Sql Server 2008 probabily do not suport this methods, try run this comand in you Sql Server, before run the procedure:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

This basic allows you to use the methods to call external services in Sql Server.

If that do not help, try see in the link this forum about compatibility ⬇
-- See about compatibility level here

Oi José....

Cara, muito obrigado pelo seu rapido retorno! Eu vou dar uma olhada na sua publicação agora mesmo!

Por enquanto, muito obrigado por compartilhar o seu conhecimento!

Abraços

Abraço, @rickheber 🤠🤙

@JahsonKim

If the data to post is not querystring mode, it is json structure writing, how to adjust this?

Thanks
Eric Xin

For some reason sp_OAGetProperty is returning null.

Am not sure why and if anyone has an idea can update the gists.
In case you are looking for an alternative to making http requests from the database, you can try SQL CLR.
Check here on how to implement SQL CLR

@ericxin1982 If you sent the post data as json string I think it will processed by the API. Ensure you also set the content type as application/json. If this doesn't work maybe you can use SQL CLR which is more flexible.