We've been using MSXML2.ServerXMLHTTP for a long time with SQL Server.
Recently it just stopped working properly.
When we execute the following code we no longer receive the status, statusText and responseHeader.
Here's some sample code:
DECLARE @ret INT,
@postData NVARCHAR(4000) = '<?xml version="1.0" encoding="UTF-8"?><note><to>Bob</to><from>Betty</from><heading>Reminder</heading><body>Hello There</body></note>',
@status NVARCHAR(32) ,
@statusText NVARCHAR(32) ,
@responseText NVARCHAR(MAX) ,
@responseHeader VARCHAR(2000)
DECLARE @token INT;
EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT;
IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);
DECLARE @url VARCHAR(255) = 'https://www.ibm.com' -- JUST DID THIS CAUSE IT WAS THE FIRST DOMAIN I THOUGHT OF
EXEC @ret = sp_OAMethod @token, 'open', NULL, 'POST', @url, 'false';
EXEC sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', 'text/xml'
EXEC sp_OAMethod @token, 'setRequestHeader', NULL, 'Accept', 'text/xml'
EXEC sp_OAMethod @token, 'setRequestHeader', NULL, 'Accept-language', 'en-CA'
EXEC @ret = sp_OAMethod @token, 'send', NULL, @postData;
IF @ret <> 0
BEGIN
EXEC sp_OAGetProperty @token, 'status', @status;
SELECT 'error' response, @status status
EXEC @ret = sp_OADestroy @token;
RETURN;
END
EXEC sp_OAGetProperty @token, 'readyState', @status;
EXEC sp_OAGetProperty @token, 'status', @status;
EXEC sp_OAGetProperty @token, 'statusText', @statusText;
EXEC sp_OAMethod @token, 'getAllResponseHeaders', @responseHeader;
DECLARE @responseTable TABLE (response VARCHAR(MAX))
INSERT INTO @responseTable (response)
EXEC sp_OAGetProperty @token, 'responseText'
SELECT TOP 1 @responseText=response FROM @responseTable
EXEC @ret = sp_OADestroy @token;
IF @ret <> 0 RAISERROR('Unable to close HTTP connection.', 10, 1);
SELECT
@status status,
@statusText statusText,
@responseText responseText,
@responseHeader responseHeader
Just execute the code, the response table should have the Status, StatusText and ResponseHeader values, but it doesn't, for me.
Stupid me forgot the OUT at the end of the property call.
Thanks to siggemannen.