Error Executing SQL Server Agent Job: JSON text is not properly formatted, Unexpected character '"'

Question

Using Microsoft SQL Server 2016 (RTM) Standard Edition, I am trying load a JSON into a SQL Server table using a SQL Server Agent Job, and get the following error:

JSON text is not properly formatted. Unexpected character '"' is found at position 508. [SQLSTATE 42000] (Error 13609).

But when I run it as T-SQL it inserts the data with out errors.

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'
      )

Can you show us the actual SQL that is being executed? The code you show isn't the same... — DavidG, Apr 10 '18 at 16:45
Hi DavidG the code posted is what I am executing. Change password and user info for security — Prospec007, Apr 10 '18 at 19:30

Weihui Guo · Answer 1 · 2020-02-26T12:25:37.697

The problem is that the JSON string, returned from the API, got truncated when executed from SQL Server Agent Job, but not from SSMS. So the unexpected character will always be something near 512.

Similarly, it'll throw a XML parsing error if the job returns XML instead of a JSON string.

The solution is to SET TEXTSIZE -1, in your SQL Server Agent Job, before executing your stored procedure. The command of your SQL Server Agent Job should be something like this:

SET TEXTSIZE -1
EXECUTE [your stored procedure]

If you are curious about the difference, you may insert your @responseText into a temp table and you'll certainly see a truncated JSON string without SET TEXTSIZE -1 if @responseText has more than 512 characters.

Error Executing SQL Server Agent Job: JSON text is not properly formatted, Unexpected character '"'

1 Answers1