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

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  gt  Options  gt  Query Results  gt  SQL Server  gt  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

User · Answer

You are out of luck  I think  THe problem is not a SQL level problem as all other answers seem to focus on  but simply one of the user interface  Management Studio is not meant to be a general purpose   generic data access interface  It is not there to be your interface  but your administrative area  and it has serious limitations handling binary data and large test data - because people using it within the specified usage profile will not run into this problem   Presenting large text data is simply not the planned usage   Your only choice would be a table valued function that takes the text input and cuts it rows for every line  so that Management Studio gets a list of rows  not a single row

User · Answer

It sounds like the Xml may not be well formed  If that is the case  then you will not be able to cast it as Xml and given that  you are limited in how much text you can return in Management Studio  However  you could break up the text into smaller chunks like so   With Tally As               Select ROW NUMBER   OVER   ORDER BY s1 object id   - 1 As Num         From sys sysobjects As s1             Cross Join sys sysobjects As s2       Select Substring T1 textCol  T2 Num   8000   1  8000  From Table As T1     Cross Join Tally As T2 Where T2 Num  lt   Ceiling Len T1 textCol    8000  Order By T2 Num   You would then need to manually combine them again   EDIT  It sounds like there are some characters in the text data that the Xml parser does not like  You could try converting those values to entities and then try the Convert xml  data  trick  So something like   Update Table Set Data   Replace Cast Data As varchar max     lt     amp lt       I needed to cast to varchar max  because the replace function will not work on text columns  There should not be any reason you couldn t convert those text columns to varchar max

User · Answer

The data type TEXT is old and should not be used anymore  it is a pain to select data out of a TEXT column   ntext  text  and image  Transact-SQL        ntext  text  and image  data types   will be removed in a future version of   Microsoft SQL Server  Avoid using   these data types in new development   work  and plan to modify applications   that currently use them  Use   nvarchar max   varchar max   and   varbinary max   instead    you need to use TEXTPTR  Transact-SQL  to retrieve the text data   Also see this article on Handling The Text Data Type

User · Answer

The simplest workaround I found is to backup the table and view the script  To do this   Right click your database and choose Tasks   Generate Scripts     Introduction  page click Next  Choose Objects  page  Choose the Select specific database objects and select your table  Click Next   Set Scripting Options  page  Set the output type to Save scripts to a specific location Select Save to file and fill in the related options Click the Advanced button Set General   Types of data to script to Data only or Schema and Data and click ok Click Next   Summary Page  click next Your sql script should be generated based on the options you set in 4 2  Open this file up and view your data

User · Answer

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      amp amp       lt      amp lt    AS XML  Details FROM Events e WHERE 1 1 AND e EventID BETWEEN 13920 AND 13930

User · Answer

I was able to get this to work     SELECT CAST   lt   CDATA     LargeTextColumn       gt   AS XML  FROM TableName

User · Answer

One work-around is to right-click on the result set and select  Save Results As      This exports it to a CSV file with the entire contents of the column  Not perfect but worked well enough for me

User · Answer

Did you try this simple solution  Only 2 clicks away   At the query window     set query options to  Results to Grid   run your query  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

User · Answer

SSMS only allows unlimited data for XML data  This is not the default and needs to be set in the options     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    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  lt  opening the XML viewer fails with a parsing error   A more robust way of doing this that avoids issues of SQL Server converting  lt  to  amp 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

User · Answer

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

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

Examples related to sql-server

Examples related to sql-server-2008

Examples related to ssms