SQL server truncates data returned as JSON

Viewed 4905

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

0 Answers
Related