SQL Ole Automation的sp_OAGetProperty获取非200响应文本异常问题

我用SQL Ole Automation提交请求到API并读取响应，常规场景运行正常，但针对某一特定API，当响应码非200时，通过sp_OAGetProperty获取responseText无法正常读取；相同请求在Postman中能得到标准JSON字符串响应。我试过调用sp_OAGetProperty @itoken, 'responseBody'获取二进制数据，但无法将其转换为可读文本。以下是调用该API的存储过程代码：

exec @iOAProcReturnCode = sp_OACreate 'MSXML2.ServerXMLHTTP', @iToken OUT; 

IF @iOAProcReturnCode <> 0 
begin 
    select @vchErrorMessage = dbo.fnConcatOAErrorMessage('Unable to open HTTP connection.', @iOAProcReturnCode);
    throw 50000, @vchErrorMessage, 1
end


-- Set up the request.
EXEC @iOAProcReturnCode = sp_OAMethod @iToken, 'open', NULL, 'POST', @vchUrl, 'false';
if @vchAuthHeader > ''
begin
    EXEC @iOAProcReturnCode = sp_OAMethod @iToken, 'setRequestHeader', Null, 'Authorization', @vchAuthHeader;
end

exec @iOAProcReturnCode = sp_OAMethod @iToken, 'setRequestHeader', null, 'Content-type', @vchContentType;

-- Send the request
EXEC @iOAProcReturnCode = sp_OAMethod @iToken, 'send', NULL, @vchBodyContent

IF @iOAProcReturnCode <> 0 
begin 
    select @vchErrorMessage = dbo.fnConcatOAErrorMessage('Unable to open connection and send request.', @iOAProcReturnCode);
    throw 50000, @vchErrorMessage, 1
end

--Read the response
--This is what fails in the case of a non 200 statusCode
insert @tResponseText
(
    vchResponse
)    
EXEC sys.sp_OAGetProperty @iToken, 'responseText'
IF @iOAProcReturnCode <> 0 
begin 
    select @vchErrorMessage = dbo.fnConcatOAErrorMessage('Unable to get response text.', @iOAProcReturnCode);
    throw 50000, @vchErrorMessage, 1
end

exec @iOAProcReturnCode = sp_OAGetProperty @iToken, 'status', @vchStatusCode OUT;
exec @iOAProcReturnCode = sp_OAGetProperty @iToken, 'statusText', @vchStatusText OUT;
IF @iOAProcReturnCode <> 0 
begin 
    select @vchErrorMessage = dbo.fnConcatOAErrorMessage('Unable to get status property.', @iOAProcReturnCode);
    throw 50000, @vchErrorMessage, 1
end

请问是否有人遇到过此类问题？我是否遗漏了关键配置或处理步骤？

解决方案 #

1. 配置忽略服务器HTTP错误 #

MSXML2.ServerXMLHTTP默认会在收到非200响应时触发错误，导致无法读取responseText。在发送请求前添加以下配置，强制对象忽略服务器错误并读取响应：

-- 在send方法调用前插入
EXEC @iOAProcReturnCode = sp_OAMethod @iToken, 'setOption', NULL, 4, 1;

参数说明：4对应SXH_OPTION_IGNORE_SERVER_ERRORS，1表示启用该选项。

2. 正确转换responseBody为可读文本 #

如果必须通过responseBody获取内容，可借助ADODB.Stream将二进制数据转为文本，需匹配API响应的编码（示例为UTF-8）：

DECLARE @responseBody VARBINARY(MAX);
EXEC sys.sp_OAGetProperty @iToken, 'responseBody', @responseBody OUT;

DECLARE @streamToken INT;
EXEC sp_OACreate 'ADODB.Stream', @streamToken OUT;
EXEC sp_OAMethod @streamToken, 'Open';
EXEC sp_OAMethod @streamToken, 'put_Type', NULL, 1; -- 设置为二进制类型
EXEC sp_OAMethod @streamToken, 'Write', NULL, @responseBody;
EXEC sp_OAMethod @streamToken, 'Position', NULL, 0;
EXEC sp_OAMethod @streamToken, 'put_Type', NULL, 2; -- 切换为文本类型
EXEC sp_OAMethod @streamToken, 'put_Charset', NULL, 'UTF-8'; -- 匹配响应编码

DECLARE @errorResponse NVARCHAR(MAX);
EXEC sp_OAMethod @streamToken, 'ReadText', @errorResponse OUT;

-- 将转换后的文本插入临时表
insert @tResponseText (vchResponse) values (@errorResponse);

EXEC sp_OADestroy @streamToken;

3. 升级MSXML对象版本 #

尝试使用更高版本的MSXML2.ServerXMLHTTP.6.0替代默认的MSXML2.ServerXMLHTTP，新版本对错误响应的兼容性更好：

exec @iOAProcReturnCode = sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @iToken OUT; 

4. 调整响应处理顺序 #

先获取状态码，再根据状态码决定读取方式，避免非200响应直接抛出错误：

-- 先读取状态码和状态文本
exec @iOAProcReturnCode = sp_OAGetProperty @iToken, 'status', @vchStatusCode OUT;
exec @iOAProcReturnCode = sp_OAGetProperty @iToken, 'statusText', @vchStatusText OUT;

-- 根据状态码分支处理
IF @vchStatusCode = 200
BEGIN
    insert @tResponseText (vchResponse)    
    EXEC sys.sp_OAGetProperty @iToken, 'responseText'
END
ELSE
BEGIN
    -- 执行上述responseBody转换逻辑
    DECLARE @responseBody VARBINARY(MAX);
    EXEC sys.sp_OAGetProperty @iToken, 'responseBody', @responseBody OUT;
    
    DECLARE @streamToken INT;
    EXEC sp_OACreate 'ADODB.Stream', @streamToken OUT;
    EXEC sp_OAMethod @streamToken, 'Open';
    EXEC sp_OAMethod @streamToken, 'put_Type', NULL, 1;
    EXEC sp_OAMethod @streamToken, 'Write', NULL, @responseBody;
    EXEC sp_OAMethod @streamToken, 'Position', NULL, 0;
    EXEC sp_OAMethod @streamToken, 'put_Type', NULL, 2;
    EXEC sp_OAMethod @streamToken, 'put_Charset', NULL, 'UTF-8';
    
    DECLARE @errorResponse NVARCHAR(MAX);
    EXEC sp_OAMethod @streamToken, 'ReadText', @errorResponse OUT;
    
    insert @tResponseText (vchResponse) values (@errorResponse);
    
    EXEC sp_OADestroy @streamToken;
END

内容的提问来源于stack exchange，提问作者ym185