title: How do I view the full content of a text or varchar(MAX) column in SQL Server 2008 Management Studio?
body: <p>In this live SQL Server 2008 (build 10.0.1600) database, there's an <code>Events</code> table, which contains a <code>text</code> column named <code>Details</code>. (Yes, I realize this should actually be a <code>varchar(MAX)</code> column, but whoever set this database up did not do it that way.) </p>
  
  <p>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.</p>
  
  <p>I've read on various locations on the Internet that you can set your Maximum Characters Retrieved for XML Data in <code>Tools &gt; Options &gt; Query Results &gt; SQL Server &gt; Results To Grid</code> to Unlimited, and then perform a query such as this:</p>
  
  <pre><code>select Convert(xml, Details) from Events
  where EventID = 13920
  </code></pre>
  
  <p>(Note that the data is column is not XML at all. <code>CONVERT</code>ing 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 <code>text</code> or <code>varchar(MAX)</code> column.)</p>
  
  <p>However, after setting the option above, running the query, and clicking on the link in the result, I still get the following error:</p>
  
  <blockquote>
    <p>Unable to show XML. The following error happened:
    Unexpected end of file has occurred. Line 5, position 220160.</p>
    
    <p>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.</p>
  </blockquote>
  
  <p>So, <strong>any idea on how to access this data?</strong> Would converting the column to <code>varchar(MAX)</code> fix my woes?</p>