CODEWITHSUNDEEP
jsonsql-serversql-agent-jobsql-agent
Prospec007
Prospec007

Reputation: 61

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

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

Upvotes: 6

Views: 7554

Answers (1)

Weihui Guo
Weihui Guo

Reputation: 3997

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.

Upvotes: 13

Related Questions