Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I view the full content of a text or varchar(MAX) column in SQL Server 2008 Management Studio?

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?

like image 501
adamjford Avatar asked May 03 '10 16:05

adamjford


People also ask

How do I get the full text of a column in SQL?

Step 2: Create Full Text Index To create a full text index choose your table and right click on that table and select “Define Full-Text Index” option. Now select Unique Index. It is compulsory that for “Full Text Index” table must have at least one unique index. Select columns name and language types for columns.

How do I find the full text catalog in SQL Server?

To get to this screen, go to the database and then go to Storage > Full Text Catalogs, find the full text catalog you want to work with and right click on it and select Properties.

How do I view the contents of a SQL database?

Right-click the Products table in SQL Server Object Explorer, and select View Data. The Data Editor launches. Notice the rows we added to the table in previous procedures. Right-click the Fruits table in SQL Server Object Explorer, and select View Data.

How do I find the maximum value of a column in SQL?

To find the max value of a column, use the MAX() aggregate function; it takes as its argument the name of the column for which you want to find the maximum value. If you have not specified any other columns in the SELECT clause, the maximum will be calculated for all records in the table.


2 Answers

SSMS only allows unlimited data for XML data. This is not the default and needs to be set in the options.

enter image description here

One trick which might work in quite limited circumstances is simply naming the column in a special manner as below so it gets treated as XML data.

DECLARE @S varchar(max) = 'A'  SET @S =  REPLICATE(@S,100000) + 'B'   SELECT @S as [XML_F52E2B61-18A1-11d1-B105-00805F49916B] 

In SSMS (at least versions 2012 to current of 18.3) this displays the results as below

enter image description here

Clicking on it opens the full results in the XML viewer. Scrolling to the right shows the last character of B is preserved,

However this does have some significant problems. Adding extra columns to the query breaks the effect and extra rows all become concatenated with the first one. Finally if the string contains characters such as < opening the XML viewer fails with a parsing error.

A more robust way of doing this that avoids issues of SQL Server converting < to &lt; etc or failing due to these characters is below (credit Adam Machanic here).

DECLARE @S varchar(max)  SELECT @S = ''  SELECT @S = @S + ' ' + OBJECT_DEFINITION(OBJECT_ID) FROM SYS.PROCEDURES  SELECT @S AS [processing-instruction(x)] FOR XML PATH('') 
like image 193
Martin Smith Avatar answered Sep 17 '22 23:09

Martin Smith


I was able to get this to work...

SELECT CAST('<![CDATA[' + LargeTextColumn + ']]>' AS XML) FROM TableName; 
like image 21
Scott Durbin Avatar answered Sep 19 '22 23:09

Scott Durbin