SQL Server JSON Truncated (Even When Using Nvarchar(Max) )

FOR JSON PATH results in SSMS truncated to 2033 characters

The behavior is documented here:

A large result set splits the long JSON string across multiple rows.

By default, SQL Server Management Studio (SSMS) concatenates the results into a single row when the output setting is Results to Grid. The SSMS status bar displays the actual row count.

Other client applications may require code to recombine lengthy results into a single, valid JSON string by concatenating the contents of multiple rows. For an example of this code in a C# application, see Use FOR JSON output in a C# client app.

Therefore, using FOR JSON to concatenate strings (when the result is longer than 2033 bytes) is not the best idea.

Try using FOR XML instead. For example:

SELECT STUFF((
    SELECT ', '+name FROM sys.columns FOR XML PATH(''), TYPE
).value('.','nvarchar(max)'),1,2,'')

nvarchar(max) still being truncated

To see the dynamic SQL generated, change to text mode (_shortcut: Ctrl-T), then use SELECT

PRINT LEN(@Query) -- Prints out 4273, which is correct as far as I can tell
--SET NOCOUNT ON
SELECT @Query

As for sp_executesql, try this (in text mode), it should show the three aaaaa...'s the middle one being the longest with 'SELECT ..' added. Watch the Ln... Col.. indicator in the status bar at bottom right showing 4510 at the end of the 2nd output.

declare @n nvarchar(max)
set @n = REPLICATE(convert(nvarchar(max), 'a'), 4500)
SET @N = 'SELECT ''' + @n + ''''
print @n   -- up to 4000
select @n  -- up to max
exec sp_Executesql @n

Long json strings truncated when returned via .net API

The returned JSON might be returned in multiple rows for lengthy results.

See Format Query Results as JSON with FOR JSON (section "Output of the FOR JSON clause") and Use FOR JSON output in a C# client app

var queryWithForJson = "SELECT ... FOR JSON";
var conn = new SqlConnection("<connection string>");
var cmd = new SqlCommand(queryWithForJson, conn);
conn.Open();
var jsonResult = new StringBuilder();
var reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
    jsonResult.Append("[]");
}
else
{
    while (reader.Read())
    {
        jsonResult.Append(reader.GetValue(0).ToString());
    }
}

SQL Server: How to Get Large amount of JSON from Store Procedure

if you are using any Cast or covert operations. Use VARCHAR(MAX) instead of VARCHAR().

I have seen this issue in such cases.

Also if you are using the print output, then it is possible that the text might get truncated, but you can use the Select or Output parameter instead.

make the output parameter of type nvarchar(max)

or check this link :

Format Query Results as JSON with FOR JSON (SQL Server)

This might help

---

Related Topics