10

The problem is, that if SQL returns JSON longer than 2033 characters it get's truncated, but if I cast it into NVARCHAR(max) on SQL side, result won't be truncated and everything will be fine.

What also is interesting, that in SSMS I have similar behavior, when I copy JSON returned as string it copies entire object, but if I copy value of JSON returned as JSON it gets truncated after N characters. But if I click on JSON as JSON result it will open full object.

Any ideas to explain such behavior?

Code, that reads data(although I am using dapper here, result will be the same with SqlCommand and ExecuteScalar/Reader):

using (var connection = new SqlConnection(_connectionString))
{
    var response = await connection.ExecuteScalarAsync<string>($"[Portal].[GetData]", commandType: CommandType.StoredProcedure);
    return response == null ? null : JObject.Parse(response);
}

Procedure, that returns result which .NET can parse correctly:

   CREATE PROCEDURE [Portal].[GetData] 
   AS
   BEGIN
    SET NOCOUNT ON;

    DECLARE @result NVARCHAR(max);
    SET @result = (SELECT TOP (120) 
        [DateTime]
        ,[Open]
        ,[Closed]
        ,[Total]
    FROM [Common].[Table]
        FOR JSON PATH, ROOT('Root'))
    SELECT @result;
   END

Result view in SSMS(result is not clickable, but can be copied without truncation):

Returned as string

Procedure, that returns truncated result(result is not clickable):

   CREATE PROCEDURE [Portal].[GetData] 
   AS
   BEGIN
    SET NOCOUNT ON;

    SELECT TOP (120) 
        [DateTime]
        ,[Open]
        ,[Closed]
        ,[Total]
    FROM [Common].[Table]
        FOR JSON PATH, ROOT('Root')
   END

Result view in SSMS:

Returned as JSON

Uriil
  • 11,948
  • 11
  • 47
  • 68
  • what is the column size of the table where you are storing the data.. if the data is larger meaning longer than what the column has be defined as, then yes..this is what will happen.. you can't store more data in a column that has a size defined smaller.. change the column to be 4000 – MethodMan Sep 20 '17 at 16:58
  • @MethodMan I am not storing it, I am reading it from SQL server in JSON format – Uriil Sep 20 '17 at 16:59
  • I am having the same issue. JSON array with 75 rows, 3 fields small fields each (like 100 characters at most) is cut mid field after 2034 characters to be exact. – John Feb 09 '20 at 16:48

0 Answers0