Azure SQL如何调用带凭据JSON Web服务并将结果写入新表

Azure SQL 数据库原生支持直接调用REST接口的存储过程sp_invoke_external_rest_endpoint，不需要额外部署中间服务、不需要配置跨服务集成，是当前场景下步骤最少、维护成本最低的实现方式。

之前报错的核心原因 #

• OPENROWSET 是本地SQL Server、Azure SQL托管实例的专属功能，单一数据库/弹性池形态的Azure SQL DB不支持该提供程序直接发起HTTP请求
• OPENJSON 仅做JSON格式解析，本身不具备网络请求能力，自然没有传递身份认证凭据的相关语法

具体实现步骤 #

• 先确认数据库兼容性级别，该存储过程要求兼容性级别为160（对应SQL Server 2022），执行以下语句开启即可：

  -- 将[你的数据库名]替换为实际业务库名
  ALTER DATABASE [你的数据库名] SET COMPATIBILITY_LEVEL = 160;
  

• 创建存储API返回结果的目标表，字段和你在Power Query里加载的20列保持类型、顺序一致即可，示例：

  CREATE TABLE [dbo].[ExternalAPIData] (
      -- 以下字段按实际API返回的列替换，共20列
      Column1 NVARCHAR(200),
      Column2 INT,
      Column3 DATETIME2,
      -- ... 补全剩余17个字段
      SyncTime DATETIME2 DEFAULT GETUTCDATE() -- 可选，记录数据同步时间
  );
  

• 创建同步用的存储过程，内置Basic认证逻辑、API调用、JSON解析、表写入全流程：

  CREATE OR ALTER PROCEDURE [dbo].[SyncExternalAPIData]
  AS
  BEGIN
      SET NOCOUNT ON;
      DECLARE @ApiResponse NVARCHAR(MAX);
      DECLARE @CallResult INT;
      DECLARE @BasicAuthHeader NVARCHAR(200);
  
      -- 生成Basic认证要求的Base64编码凭据
      SET @BasicAuthHeader = N'Basic ' + (
          SELECT CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("bin")))', 'NVARCHAR(100)')
          FROM (SELECT CAST('u:p' AS VARBINARY(200)) AS bin) AS CredentialBin
      );
  
      -- 发起API调用
      EXEC @CallResult = sp_invoke_external_rest_endpoint
          @url = N'https://something.something.com/api/1',
          @method = N'GET',
          @headers = N'{"Authorization": "' + @BasicAuthHeader + '"}',
          @response = @ApiResponse OUTPUT;
  
      -- 调用失败直接抛出错误
      IF @CallResult <> 0
      BEGIN
          THROW 50001, N'API调用失败，请检查凭据、地址是否有效', 1;
      END
  
      -- 清空表内旧数据，需要增量同步可替换这部分逻辑
      TRUNCATE TABLE [dbo].[ExternalAPIData];
  
      -- 解析JSON写入目标表
      INSERT INTO [dbo].[ExternalAPIData] (Column1, Column2, Column3 /* 补全剩余17个列名 */)
      SELECT Column1, Column2, Column3 /* 补全剩余17个列名 */
      FROM OPENJSON(@ApiResponse, N'$') 
      -- 如果API返回的结果数组嵌套在某个字段下（比如{"data": [结果数组]}），就把上面的路径改成'$.data'，和你Power Query里解析的路径对齐即可
      WITH (
          Column1 NVARCHAR(200) N'$.col1',
          Column2 INT N'$.col2',
          Column3 DATETIME2 N'$.col3'
          -- ... 补全剩余17个字段的JSON路径映射，和API返回的字段key一一对应
      );
  END
  GO
  

• 执行验证：存储过程创建完成后，直接执行以下命令即可完成全量拉取写入：

  EXEC [dbo].[SyncExternalAPIData];
  SELECT * FROM [dbo].[ExternalAPIData];
  

备选方案（仅当当前Azure SQL版本不支持上述存储过程时使用） #

如果你的Azure SQL实例版本过老不支持sp_invoke_external_rest_endpoint，次选零代码方案是配置Azure Logic Apps：

• 触发器选按需/定时触发
• 第一步添加HTTP动作，填入API地址、Basic认证的用户名密码
• 第二步添加解析JSON动作，把HTTP返回的JSON导入Schema
• 第三步添加Azure SQL写入动作，把解析后的字段映射到目标表即可
  全程拖拽配置，不需要写业务代码，也能满足需求。

不要尝试在Azure SQL DB里启用OLE Automation、自定义CLR等老方案实现HTTP调用，这些功能在Azure SQL中要么默认禁用，要么存在安全和权限限制，配置成本远高于上述方案。

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