How do I send Parameters by using HTTP POST with T-SQL and OLE Automation Procedures

Question

I'm using the OLE Automation Procedures to send a HTTP POST Request to a SOAP-Webservice and process the returned data. This works fine for me by using the code snipped below. Now I need to pass a POST-parameter over to the webservice. Any idea how i could do that?

Parameter: searchstring
Value: V34432221

DECLARE @XMLResponse xml
DECLARE @obj INT
DECLARE @ValorDeRegreso INT
DECLARE @sUrl NVARCHAR(200)
DECLARE @response VARCHAR(MAX)
DECLARE @hr INT
DECLARE @src NVARCHAR(255)
DECLARE @desc NVARCHAR(255)
SET @sUrl = 'http://server/Webservice/Service1.asmx/Suche'
EXEC sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
EXEC sp_OAMethod @obj, 'Open', NULL, 'POST', @sUrl, false
EXEC sp_OAMethod @obj, 'send'
EXEC sp_OAGetProperty @obj, 'responseText', @XMLResponse OUT
--process @XMLResponse...

Answer 1

EXEC sp_OAMethod @obj, 'send', NULL, @StringRequest works perfectly. This is my code, if it can be helpfull :

Declare @Objet as Int
Declare @Reponse as Varchar(8000)
Declare @url VARCHAR(3000)
Declare @Parametres VARCHAR(3000)
Declare @Erreur int
Declare @MessageErreur varchar(255)
Declare @MessageErreurAvecNumero varchar(255)
Declare @Statut int

SET @url = 'http://localhost/MonWebservice/MaPage.asmx/maFonction'
SET @Parametres = 'theIDMachin=' + cast('268029' as varchar)
SET @Parametres = @Parametres + '&theTypeMachin=' + cast('1' as varchar)
SET @Parametres = @Parametres + '&theBooleen=False'
SET @Parametres = @Parametres + '&theStatut=' + cast('0' as varchar)

exec @Erreur = sp_OACreate 'MSXML2.ServerXMLHttp', @Objet OUT;
if @Erreur <> 0 begin set @MessageErreur = 'sp_OACreate MSXML2.ServerXMLHttp.3.0 failed' goto fin end

exec @Erreur = sp_OAMethod @Objet, 'open', NULL, 'POST', @url, false            
if @Erreur <> 0 begin set @MessageErreur = 'sp_OAMethod Open failed' goto fin end

exec @Erreur = sp_OAMethod @Objet, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
if @Erreur <> 0 begin set @MessageErreur = 'sp_OAMethod setRequestHeader failed' goto fin end

exec @Erreur = sp_OAMethod @Objet, 'send', NULL, @Parametres
if @Erreur <> 0 begin set @MessageErreur = 'sp_OAMethod Send failed' goto fin end

exec @Erreur = sp_OAGetProperty @Objet, 'status', @Statut OUT
if @Erreur <> 0 begin set @MessageErreur = 'sp_OAGetProperty read status failed' goto fin end

exec @Erreur = sp_OAMethod @Objet, 'responseText', @Reponse OUTPUT
if @Erreur <> 0 begin set @MessageErreur = 'sp_OAMethod read response failed' goto fin end

select @Reponse

exec sp_OADestroy @Objet
return

fin:
print @MessageErreur
exec sp_OADestroy @Objet
SET @MessageErreurAvecNumero = 'Erreur : ' + cast(@Erreur as varchar) + ' - ' + >@MessageErreur
Raiserror(@MessageErreurAvecNumero, 16, 1)
return

Answer 2

Maybe this works:

DECLARE @XMLResponse xml
DECLARE @obj INT
DECLARE @ValorDeRegreso INT
DECLARE @sUrl NVARCHAR(200)
DECLARE @response VARCHAR(MAX)
DECLARE @hr INT
DECLARE @src NVARCHAR(255)
DECLARE @desc NVARCHAR(255)
DECLARE @StringRequest NVARCHAR(500)
SET @StringRequest = 'searchstring=V34432221'
SET @sUrl = 'http://server/Webservice/Service1.asmx/Suche'
EXEC sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
EXEC sp_OAMethod @obj, 'Open', NULL, 'POST', @sUrl, false
EXEC sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
EXEC sp_OAMethod @obj, 'send', NULL, @StringRequest
EXEC sp_OAGetProperty @obj, 'responseText', @XMLResponse OUT
--process @XMLResponse...

Answer 3

So I know this is like 3 years later and you probably don't have this issue anymore... but I'm replying because I ran into this problem and it took me way too much time searching for the answer to your question. So on the off-chance some poor sap comes along looking for the same question, they will have an answer.

EDIT: You must make sure you enable OLE automation before this code would work.

EXEC sp_configure 'Ole Automation Procedures', 1;
GO

RECONFIGURE;
GO

You can execute an HTTP Post from T-SQL using a variation of this:

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://requestb.in/16xdq1p1'

-- 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);

I've got to give credit to the writer of the blog post that has this code.