FOR JSON PATH results in SSMS truncated to 2033 characters

by

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

More Related Contents:

  1. What is the use of GO in SQL Server Management Studio & Transact SQL?
  2. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  3. SQL Server – error when creating stored procedure
  4. How can foreign key constraints be temporarily disabled using T-SQL?
  5. Does T-SQL have an aggregate function to concatenate strings? [duplicate]
  6. Datatype for storing ip address in SQL Server
  7. Is there a way to loop through a table variable in TSQL without using a cursor?
  8. sql server invalid object name – but tables are listed in SSMS tables list
  9. Maximum size of a varchar(max) variable
  10. best way to convert and validate a date string
  11. How to get a date in YYYY-MM-DD format from a TSQL datetime field?
  12. Is there a way to simplify a NULL compare of 2 values
  13. How do you specify a different port number in SQL Management Studio?
  14. TSQL Try / Catch within Transaction or vice versa?
  15. What is the maximum number of characters that nvarchar(MAX) will hold?
  16. TSQL: Create a view that accesses multiple databases
  17. Sequence as default value for a column
  18. Aggregate bitwise-OR in a subquery
  19. How do I flush the PRINT buffer in TSQL?
  20. OR Operator Short-circuit in SQL Server
  21. How to change default language for SQL Server?
  22. T-SQL incrementing counter based on change in a column value
  23. Removing the remembered login and password list in SQL Server Management Studio
  24. T-SQL XOR Operator
  25. The request failed or the service did not respond in a timely fashion?
  26. Does query plan optimizer works well with joined/filtered table-valued functions?
  27. SQL Server 2008 can’t login with newly created user
  28. How to set value to variable using ‘execute’ in t-sql?
  29. Update SQL with consecutive numbering
  30. Count number of records returned by group by

Leave a Comment