How do you view ALL text from an ntext or nvarchar(max) in SSMS? How do you view ALL text from an ntext or nvarchar(max) in SSMS? sql-server sql-server

How do you view ALL text from an ntext or nvarchar(max) in SSMS?


I was able to get the full text (99,208 chars) out of a NVARCHAR(MAX) column by selecting (Results To Grid) just that column and then right-clicking on it and then saving the result as a CSV file. To view the result open the CSV file with a text editor (NOT Excel). Funny enough, when I tried to run the same query, but having Results to File enabled, the output was truncated using the Results to Text limit.

The work-around that @MartinSmith described as a comment to the (currently) accepted answer didn't work for me (got an error when trying to view the full XML result complaining about "The '[' character, hexadecimal value 0x5B, cannot be included in a name").


Quick trick-

SELECT CAST('<A><![CDATA[' + CAST(LogInfo as nvarchar(max)) + ']]></A>' AS xml)FROM LogsWHERE IDLog = 904862629


Options (Query Results/SQL Server/Results to Grid Page)

To change the options for the current queries, click Query Options on the Query menu, or right-click in the SQL Server Query window and select Query Options.

...

Maximum Characters Retrieved
Enter a number from 1 through 65535 to specify the maximum number of characters that will be displayed in each cell.

Maximum is, as you see, 64k. The default is much smaller.

BTW Results to Text has even more drastic limitation:

Maximum number of characters displayed in each column
This value defaults to 256. Increase this value to display larger result sets without truncation. The maximum value is 8,192.