title: Send requests to rest API from SQL Server and check http code returned
body: <p>I'm attempting to send json data to a rest API, ideally from SQL Server 2016.  I have come across the following code (from <a href="https://gist.github.com/theorigin/fa3c58406ff7b4565ca2" rel="nofollow noreferrer">https://gist.github.com/theorigin/fa3c58406ff7b4565ca2</a>) that uses sp_OAMethod and have tested it using webhook.site so I know it sends, but I can't see the http response code from SQL which is what I need to view... the API in question sends a 400 response along with an error message if there is an issue with a submission, or a 200 if everything was correct.</p>
  
  <p>The SQL code returns a NULL column under all scenarios. At present I have a PHP application that I have written that does all this for me, except it is not automated. The application runs through csv files and sends json one line at a time to the endpoint. Before I alter it to look at query results rather than csv files, and before I start looking at adding this to a cron job (or perhaps a scheduled task in Windows?), I have been wondering if what I am trying to achieve is possible using SQL Server, which is where all my data is stored. This may be a round peg into a square hole though and if so then I will discount this method.</p>
  
  <pre><code>DECLARE @Object AS INT;
  DECLARE @ResponseText AS VARCHAR(8000);
  DECLARE @Body AS VARCHAR(8000) = 
  '{
      "name": "John",
      "age": 30,
  }'  
  
  EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
  EXEC sp_OAMethod @Object, 'open', NULL, 'post','https://webhook.site/1234', 'false'
  
  EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'
  EXEC sp_OAMethod @Object, 'send', null, @body
  
  EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
  SELECT @ResponseText
  
  EXEC sp_OADestroy @Object
  </code></pre>
  
  <p>Ideally I will see '200' or '400' as a response for each request and I can parse these after the stored procedure has run.</p>
  
  <p>Any help or suggestions are very welcome.
  Thanks</p>