MSSQLSERVER_SOAP_Requests.sql

-- Enable OLE Automation Procedures if not already enabled.
-- This is a server-wide setting and has security implications.
-- EXEC sp_configure 'Ole Automation Procedures', 1;
-- RECONFIGURE;
-- GO

-- Declare variables for the HTTP request
DECLARE @url VARCHAR(255) = 'http://www.dneonline.com/calculator.asmx'; -- Example SOAP service URL
DECLARE @soapAction VARCHAR(255) = 'http://tempuri.org/Add'; -- The SOAPAction header for the method
DECLARE @xmlPayload NVARCHAR(MAX); -- The XML payload for the SOAP request
DECLARE @responseText NVARCHAR(MAX); -- To store the response from the web service
DECLARE @obj INT; -- Object variable for the MSXML2.XMLHTTP object
DECLARE @hr INT; -- HRESULT from COM calls
DECLARE @httpStatus INT; -- To store the HTTP status code (e.g., 200, 404, 500)
DECLARE @httpStatusText NVARCHAR(255); -- To store the HTTP status text (e.g., 'OK', 'Not Found')

-- Define the XML payload for the SOAP request
-- This is an example for a simple 'Add' operation on a public calculator web service.
SET @xmlPayload = N'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <Add xmlns="http://tempuri.org/">
      <intA>10</intA>
      <intB>25</intB>
    </Add>
  </soap:Body>
</soap:Envelope>';

PRINT 'Sending SOAP request to: ' + @url;
PRINT 'SOAP Action: ' + @soapAction;
PRINT 'Payload Length: ' + CAST(LEN(@xmlPayload) AS VARCHAR(10)) + ' characters';

-- Create the MSXML2.XMLHTTP object
-- This object is used to send HTTP requests and receive responses.
EXEC @hr = sp_OACreate 'MSXML2.XMLHTTP', @obj OUT;
IF @hr <> 0
BEGIN
    PRINT 'Error creating MSXML2.XMLHTTP object (HRESULT: ' + CAST(@hr AS VARCHAR(10)) + ').';
    EXEC sp_OADestroy @obj; -- Clean up in case of error
    RETURN;
END

-- Open the HTTP request (Method, URL, Async, User, Password)
-- 'POST' is used for SOAP requests. 'False' means synchronous (waits for response).
EXEC @hr = sp_OAMethod @obj, 'open', NULL, 'POST', @url, FALSE;
IF @hr <> 0
BEGIN
    PRINT 'Error opening HTTP request (HRESULT: ' + CAST(@hr AS VARCHAR(10)) + ').';
    EXEC sp_OADestroy @obj;
    RETURN;
END

-- Set request headers
-- 'Content-Type' is crucial for SOAP, specifying XML and encoding.
EXEC @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'text/xml; charset=utf-8';
IF @hr <> 0
BEGIN
    PRINT 'Error setting Content-Type header (HRESULT: ' + CAST(@hr AS VARCHAR(10)) + ').';
    EXEC sp_OADestroy @obj;
    RETURN;
END

-- 'SOAPAction' is also required by many SOAP services.
EXEC @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'SOAPAction', @soapAction;
IF @hr <> 0
BEGIN
    PRINT 'Error setting SOAPAction header (HRESULT: ' + CAST(@hr AS VARCHAR(10)) + ').';
    EXEC sp_OADestroy @obj;
    RETURN;
END

-- Send the request with the XML payload
PRINT 'Sending request...';
EXEC @hr = sp_OAMethod @obj, 'send', NULL, @xmlPayload;
IF @hr <> 0
BEGIN
    PRINT 'Error sending HTTP request (HRESULT: ' + CAST(@hr AS VARCHAR(10)) + '). This might indicate network issues or an invalid URL.';
    EXEC sp_OADestroy @obj;
    RETURN;
END

-- Get HTTP Status and Status Text
-- These properties provide information about the response received from the server.
EXEC @hr = sp_OAGetProperty @obj, 'status', @httpStatus OUT;
IF @hr <> 0
BEGIN
    PRINT 'Error getting HTTP status (HRESULT: ' + CAST(@hr AS VARCHAR(10)) + ').';
    EXEC sp_OADestroy @obj;
    RETURN;
END

EXEC @hr = sp_OAGetProperty @obj, 'statusText', @httpStatusText OUT;
IF @hr <> 0
BEGIN
    PRINT 'Error getting HTTP status text (HRESULT: ' + CAST(@hr AS VARCHAR(10)) + ').';
    EXEC sp_OADestroy @obj;
    RETURN;
END

PRINT '-----------------------------------------';
PRINT 'HTTP Status: ' + CAST(@httpStatus AS VARCHAR(10));
PRINT 'HTTP Status Text: ' + @httpStatusText;

-- Retrieve the response text
-- This is where the original error occurred. We now have more context.
EXEC @hr = sp_OAGetProperty @obj, 'responseText', @responseText OUT;
IF @hr <> 0
BEGIN
    PRINT 'Error getting response text (HRESULT: ' + CAST(@hr AS VARCHAR(10)) + ').';
    PRINT 'Possible reasons: Server did not return a text response, or response was malformed/empty.';
END
ELSE
BEGIN
    -- Print the response only if successfully retrieved
    PRINT 'Web Service Response:';
    PRINT @responseText;
END


-- Clean up the COM object
-- It's important to destroy the object to release resources.
EXEC @hr = sp_OADestroy @obj;
IF @hr <> 0
BEGIN
    PRINT 'Error destroying MSXML2.XMLHTTP object (HRESULT: ' + CAST(@hr AS VARCHAR(10)) + ').';
    RETURN;
END