title: SQL API call through sp_OAMethod causing error
body: <p>I'm looking to add an item to our systems through an API call to the supplier's site on given conditions.  We have a replicated database and I want the process to be automated.</p>
  
  <p>The supplier's, surprisingly, require you to login on one address then navigate to another to call the SEND but I don't think that the session is being persisted.</p>
  
  <p>The code below is the SP I'm trying to create, which would allow me to use it for a variety of updates, not just this specific type.</p>
  
  <p>The code I have so far is:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[CallAPI]
                          (
                           @Address nvarchar(MAX)
                          ,@Message nvarchar(MAX)
                          )
  AS
  
  DECLARE @url_login nvarchar(MAX) = 'https://credentials'
  DECLARE @Login nvarchar(MAX) = '{"username": "uSeR","password": "pAsS"}'
  DECLARE @obj int
  DECLARE @hResult int
  DECLARE @statusText varchar(1000)
  DECLARE @status varchar(1000) = 200
  
  ---------------------------------------------------------------------------------------------------
  EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
  ---------------------------------------------------------------------------------------------------
  --Log in
  EXEC @hResult = sp_OAMethod @obj, 'Open', NULL, 'post', @url_login, false
  EXEC @hResult = sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/json'
  EXEC @hResult = sp_OAMethod @obj, 'setRequestHeader', NULL, 'api-key', 'APIKey'
  EXEC @hResult = sp_OAMethod @obj, 'setRequestHeader', NULL, 'connection', 'keep - alive'
  EXEC @hResult = sp_OAMethod @obj, 'setRequestHeader', NULL, 'cookie', 'monster'
  EXEC @hResult = sp_OAMethod @obj, 'send', NULL, @Login
  EXEC sp_OAGetProperty @obj, 'StatusText', @statusText out
  EXEC sp_OAGetProperty @obj, 'Status', @status out
  IF @status = 200 
      BEGIN
          print 'Successful log-in'
          -------------------------------------------------------------------------------------------
          --Navigate to required address
          EXEC @hResult = sp_OAMethod @obj, 'Open', NULL, 'post', @Address, false
          EXEC @hResult = sp_OAMethod @obj, 'setRequestHeader', NULL, 'cookie', 'monster'
          EXEC sp_OAGetProperty @obj, 'StatusText', @statusText out
          EXEC sp_OAGetProperty @obj, 'Status', @status out
          IF @status = 200 
              BEGIN
                  -----------------------------------------------------------------------------------
                  --Post message
                  print 'Successful navigate'
                  EXEC @hResult = sp_OAMethod @obj, 'send', NULL, @Message
                  EXEC sp_OAGetProperty @obj, 'StatusText', @statusText out
                  EXEC sp_OAGetProperty @obj, 'Status', @status out
                  IF @status = 200 
                      print 'Successful posting'
              END
      END
  
  IF @status &lt;&gt; 200
      SELECT @status, @statusText 
  
  EXEC sp_OADestroy @obj
  </code></pre>