Ошибка вставки JSON в таблицу SQL Server: неожиданный символ "" "

Question

Используя стандартную версию Microsoft SQL Server 2016 (RTM), я пытаюсь загрузить JSON в таблицу SQL Server с помощью задания агента 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

Проблема в том, что строка JSON, возвращаемая из API, усекалась при выполнении из задания агента SQL Server, но не из SSMS. Таким образом, неожиданный символ всегда будет примерно 512.

Точно так же он бросит XML parsingошибка, если задание возвращает XML вместо строки JSON.

Решение состоит в том, чтобы SET TEXTSIZE -1 в задании агента SQL Server перед выполнением хранимой процедуры. Команда вашего задания агента SQL Server должна быть примерно такой:

SET TEXTSIZE -1
EXECUTE [your stored procedure]

Если вас интересует разница, вы можете вставить свой @responseText во временную таблицу, и вы обязательно увидите усеченную строку JSON без SET TEXTSIZE -1 если @responseText имеет более 512 знаков.