调用Rest API时sp_OAMethod始终返回NULL的问题求助

1. 检查SQL Server网络访问权限 #

SQL Server服务账户可能无权限访问外部HTTPS站点，或被防火墙/代理拦截：

• 确认SQL Server运行账户（如Local System、Network Service）能正常访问互联网；
• 若使用代理，需在代码中添加代理配置：

  EXEC sp_OAMethod @objectID, N'setProxy', NULL, 2, N'http://你的代理地址:端口';
  

2. 验证SSL证书信任 #

cepaberto.com的SSL证书可能未被服务器信任，导致请求静默失败：

• 在服务器上手动访问API URL，确认浏览器是否提示证书问题；
• 若证书不受信任，将对应根证书导入服务器本地计算机\受信任的根证书颁发机构存储。

3. 补充错误捕获与调试逻辑 #

原代码未捕获OLE对象错误，无法定位问题，修改代码添加错误检查：

CREATE PROCEDURE MakeCEPAbertoRequest
    @cep VARCHAR(8) -- Tamanho do CEP no Brasil
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @token VARCHAR(1000) = 'Token token=d142c65bc45454595b15897e1d70c04b6';
    DECLARE @url VARCHAR(1000) = 'https://www.cepaberto.com/api/v3/cep?cep=' + @cep;
    DECLARE @requestResult NVARCHAR(MAX);
    DECLARE @Stat INT;
    DECLARE @errorSource NVARCHAR(255);
    DECLARE @errorDescription NVARCHAR(255);
    
    -- 使用新版本XMLHTTP对象
    DECLARE @objectID INT;
    EXEC @Stat = sp_OACreate N'MSXML2.ServerXMLHTTP.6.0', @objectID OUT;
    IF @Stat <> 0
    BEGIN
        EXEC sp_OAGetErrorInfo @objectID, @errorSource OUT, @errorDescription OUT;
        SELECT '创建对象失败: ' + @errorDescription AS ErrorMessage;
        RETURN;
    END
    
    -- 打开连接
    EXEC @Stat = sp_OAMethod @objectID, N'open', NULL, N'GET', @url, false;
    IF @Stat <> 0
    BEGIN
        EXEC sp_OAGetErrorInfo @objectID, @errorSource OUT, @errorDescription OUT;
        SELECT '打开连接失败: ' + @errorDescription AS ErrorMessage;
        EXEC sp_OADestroy @objectID;
        RETURN;
    END
    
    -- 设置请求头
    EXEC @Stat = sp_OAMethod @objectID, N'setRequestHeader', NULL, 'Content-Type', 'application/json';
    IF @Stat <> 0
    BEGIN
        EXEC sp_OAGetErrorInfo @objectID, @errorSource OUT, @errorDescription OUT;
        SELECT '设置Content-Type失败: ' + @errorDescription AS ErrorMessage;
        EXEC sp_OADestroy @objectID;
        RETURN;
    END
    
    EXEC @Stat = sp_OAMethod @objectID, N'setRequestHeader', NULL, N'Authorization', @token;
    IF @Stat <> 0
    BEGIN
        EXEC sp_OAGetErrorInfo @objectID, @errorSource OUT, @errorDescription OUT;
        SELECT '设置Authorization失败: ' + @errorDescription AS ErrorMessage;
        EXEC sp_OADestroy @objectID;
        RETURN;
    END
    
    -- 发送请求
    EXEC @Stat = sp_OAMethod @objectID, N'send';
    IF @Stat <> 0
    BEGIN
        EXEC sp_OAGetErrorInfo @objectID, @errorSource OUT, @errorDescription OUT;
        SELECT '发送请求失败: ' + @errorDescription AS ErrorMessage;
        EXEC sp_OADestroy @objectID;
        RETURN;
    END
    
    -- 获取响应状态码
    DECLARE @statusCode INT;
    EXEC sp_OAMethod @objectID, N'status', @statusCode OUT;
    SELECT @statusCode AS StatusCode;
    
    -- 获取响应内容
    EXEC @Stat = sp_OAMethod @objectID, N'responseText', @requestResult OUTPUT;
    IF @Stat <> 0
    BEGIN
        EXEC sp_OAGetErrorInfo @objectID, @errorSource OUT, @errorDescription OUT;
        SELECT '获取响应失败: ' + @errorDescription AS ErrorMessage;
        EXEC sp_OADestroy @objectID;
        RETURN;
    END

    EXEC sp_OADestroy @objectID;
    SELECT @requestResult AS Response
END
GO

• 新增错误捕获可直接返回具体问题原因；
• 添加状态码检查，确认API是否返回非200状态；
• 改用MSXML2.ServerXMLHTTP.6.0提升HTTPS兼容性。

4. CLR存储过程额外检查 #

若CLR版本也返回NULL，需确认：

• CLR存储过程权限设置为EXTERNAL_ACCESS或UNSAFE（默认SAFE无法访问外部网络）；
• 代码中是否处理SSL证书验证（测试环境可临时忽略验证，生产环境不建议）：

  ServicePointManager.ServerCertificateValidationCallback += (sender, cert, chain, sslPolicyErrors) => true;
  

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