执行 SQL Server 代理作业时出错：JSON 文本格式不正确，意外字符'"'

Question

使用

SQL Server 2016 (RTM( Standard Edition Microsoft，我尝试使用 SQL Server 代理作业将 JSON 加载到 SQL Server 表中，并收到以下错误：

JSON 文本格式不正确。在位置 508 处发现意外字符 '"'。[SQLSTATE 42000](错误 13609(。

但是当我将其作为 T-SQL 运行时，它会插入数据而没有错误。

DECLARE @return_value int,
    @responseText nvarchar(MAX),
    @json nvarchar(MAX)
EXEC    @return_value = [dbo].[HTTPRequest]
    @URI = N'http://flexapi.foresightgps.com/ForesightFlexAPI.ashx',
    @methodName = N'post',
    @requestBody =     N'{"method":"GetTagTempHistory","conncode":"PROVIDER","code":"USERNAME","wsuserid":"USERID" }',
    @SoapAction = 'MSXML2.ServerXMLHTTP',
    @UserName = N'USERNAME',
    @Password = N'PASSWORD',
    @responseText = @responseText OUTPUT
--SELECT    @responseText as '@responseText';
  SELECT    @json= VALUE FROM OPENJSON(@responseText) WHERE [key]='ForesightFlexAPI';
INSERT INTO Localizado([TransactionID],[TrueTime],[Temp],[Name],[yLat],[xLong],[Speed],[Ignition],[Location])
SELECT [TransactionID],[TrueTime],[Temp],[Name],[yLat],[xLong],[Speed],[Ignition],[Location]
FROM OPENJSON(@json,'lax $.DATA')
WITH( TransactionID nvarchar(20) '$.TransactionID',
      TrueTime NVARCHAR(50) '$.TrueTime',
      Temp decimal(9,4) '$.Temp',
      Name nvarchar(50) '$.Name',
      yLat nvarchar(50) '$.yLat',
      xLong nvarchar(50) '$.xLong',
      Speed nvarchar(20) '$.Speed',
      Ignition nvarchar(20) '$.Ignition',
      Location nvarchar(500) 'lax $.Location'
      )

Answer 1

问题是，从 API 返回的 JSON 字符串在从 SQL Server 代理作业执行时被截断，而不是从 SSMS 执行。所以意想不到的角色总是在 512 附近。

同样，如果作业返回 XML 而不是 JSON 字符串，则会引发XML parsing错误。

解决方案是在执行存储过程之前在 SQL Server 代理作业中SET TEXTSIZE -1。SQL Server 代理作业的命令应如下所示：

SET TEXTSIZE -1
EXECUTE [your stored procedure]

如果您对差异感到好奇，您可以将@responseText插入临时表中，如果@responseText的字符超过 512 个字符，您肯定会看到一个截断的 JSON 字符串，而不会SET TEXTSIZE -1。