How do I view the full content of a text or varcha

Question

In this live SQL Server 2008 (build 10.0.1600) database, there's an Events table, which contains a text column named Details. (Yes, I realize this should actually be a varchar(MAX) column, but whoever set this database up did not do it that way.)

This column contains very large logs of exceptions and associated JSON data that I'm trying to access through SQL Server Management Studio, but whenever I copy the results from the grid to a text editor, it truncates it at 43679 characters.

I've read on various locations on the Internet that you can set your Maximum Characters Retrieved for XML Data in Tools > Options > Query Results > SQL Server > Results To Grid to Unlimited, and then perform a query such as this:

select Convert(xml, Details) from Events
where EventID = 13920

(Note that the data is column is not XML at all. CONVERTing the column to XML is merely a workaround I found from Googling that someone else has used to get around the limit SSMS has from retrieving data from a text or varchar(MAX) column.)

However, after setting the option above, running the query, and clicking on the link in the result, I still get the following error:

Unable to show XML. The following error happened: Unexpected end of file has occurred. Line 5, position 220160.

One solution is to increase the number of characters retrieved from the server for XML data. To change this setting, on the Tools menu, click Options.

So, any idea on how to access this data? Would converting the column to varchar(MAX) fix my woes?

Answer 1

Did you try this simple solution? Only 2 clicks away!

At the query window,

1. set query options to "Results to Grid", run your query
2. Right click on the results tab at the grid corner, save results as any files

You will get all the text you want to see in the file!!! I can see 130,556 characters for my result of a varchar(MAX) field

Answer 2

Starting from SSMS 18.2, you can now view up to 2 million characters in the grid results. Source

Allow more data to be displayed (Result to Text) and stored in cells (Result to Grid). SSMS now allows up to 2M characters for both.

I verified this with the code below.

DECLARE @S varchar(max) = 'A'

SET @S =  REPLICATE(@S,2000000) + 'B' 

SELECT @S as a

Answer 3

The simplest workaround I found is to backup the table and view the script. To do this

1. Right click your database and choose Tasks > Generate Scripts...
2. "Introduction" page click Next
3. "Choose Objects" page
   1. Choose the Select specific database objects and select your table.
   2. Click Next
4. "Set Scripting Options" page
   1. Set the output type to Save scripts to a specific location
   2. Select Save to file and fill in the related options
   3. Click the Advanced button
   4. Set General > Types of data to script to Data only or Schema and Data and click ok
   5. Click Next
5. "Summary Page" click next
6. Your sql script should be generated based on the options you set in 4.2. Open this file up and view your data.

Answer 4

I prefer this simple XML hack which makes columns clickable in SSMS on a cell-by-cell basis. With this method, you can view your data quickly in SSMS’s tabular view and click on particular cells to see the full value when they are interesting. This is identical to the OP’s technique except that it avoids the XML errors.

SELECT
     e.EventID
    ,CAST(REPLACE(REPLACE(e.Details, '&', '&amp;'), '<', '&lt;') AS XML) Details
FROM Events e
WHERE 1=1
AND e.EventID BETWEEN 13920 AND 13930
;