SQL Server调用GET API响应超1024KB报错 需保留SQL方案解决

这个报错来源于你调用的MSXML2.ServerXMLHTTP.6.0 COM组件的默认响应大小限制，其默认responseBufferLimit参数值为1024KB，当API返回内容超过该阈值就会触发此提示，和SQL Server本身nvarchar(max)类型的存储上限无关。

方案1：调整responseBufferLimit参数（最简便，优先尝试） #

直接修改COM组件的响应缓冲区上限参数，不需要改动原有逻辑的其他部分。只需要在open方法调用完成后、send方法调用前，新增一行设置缓冲区上限的代码即可。
参数设为0代表无大小限制，你也可以根据实际需求设为指定大小（比如设置为4194304代表4MB上限）。
修改后的完整代码如下：

DECLARE @Object AS Int;
DECLARE @hr INT
DECLARE @json AS TABLE(Json_Table nvarchar(max))
declare @username varchar(50), @password varchar(50), @encoded_base64 varchar(max), @auth varchar(max)
declare @source varbinary(max)

set @username = 'demousername'
set @password = 'demopassword'
SET @source = CONVERT(varbinary(max), @username + ':' + @password)
SET @encoded_base64 = CAST(N'' AS xml).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)')
SET @auth = 'Basic ' + @encoded_base64

Exec @hr=sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @Object OUT;
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'open', NULL, 'get',
                 'http://demourl',
                 'false'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
-- 新增行：设置响应缓冲区无上限
Exec @hr=sp_OASetProperty @Object, 'responseBufferLimit', 0
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
-- 原有逻辑不变
EXEC @hr = sp_OAMethod @Object, 'setRequestHeader', NULL, 'Authorization', @auth;
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'send'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'responseText', @json OUTPUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object

INSERT into @json (Json_Table) exec sp_OAGetProperty @Object, 'responseText'
select * from @json
SELECT * FROM OPENJSON((select * from @json), N'$.data')
WITH (   
      [Column1] nvarchar(max) N'$.column1.id'
)
EXEC sp_OADestroy @Object

方案2：通过ADODB.Stream分块读取（兼容特殊权限场景） #

如果因为实例权限限制无法修改responseBufferLimit参数，可以改用读取二进制响应再转文本的方式规避限制：

-- 原有创建ServerXMLHTTP、open、设置header、send的逻辑不变，send之后替换原responseText读取逻辑
DECLARE @stream INT, @responseText NVARCHAR(MAX)
Exec @hr=sp_OACreate 'ADODB.Stream', @stream OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @stream
Exec @hr=sp_OASetProperty @stream, 'Type', 1 -- 二进制模式
IF @hr <> 0 EXEC sp_OAGetErrorInfo @stream
Exec @hr=sp_OAMethod @stream, 'Open'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @stream
-- 将响应写入流
Exec @hr=sp_OAGetProperty @Object, 'responseBody', @responseText OUT
Exec @hr=sp_OAMethod @stream, 'Write', NULL, @responseText
IF @hr <> 0 EXEC sp_OAGetErrorInfo @stream
Exec @hr=sp_OASetProperty @stream, 'Position', 0
Exec @hr=sp_OASetProperty @stream, 'Type', 2 -- 文本模式
Exec @hr=sp_OASetProperty @stream, 'Charset', 'UTF-8' -- 根据实际API返回编码调整
SET @responseText = sp_OAMethod @stream, 'ReadText'
INSERT into @json (Json_Table) VALUES (@responseText)
-- 销毁流对象
Exec sp_OADestroy @stream
-- 后续OPENJSON逻辑不变

• 两种方案都不需要引入SQL之外的其他工具，完全符合你的技术限制要求
• 若API返回的编码不是UTF-8，方案2中需要把Charset参数调整为对应的编码（比如GBK、GB2312）

内容的提问来源于stack exchange，提问作者Syed Hussain Itiba Naqvi