Today’s post is prompted by a question I was asked by one of the fellow team members recently. Most enterprise applications have a “comments” or “memo” field used to store free-form text comments entered by the users, and the product that I work on is no different. Depending upon whether or not Unicode support is required, VARCHAR(MAX) or NVARCHAR(MAX) are the general choice for storage of these fields. As part of a troubleshooting exercise, one of the engineers was attempting to query this data in SSMS for exporting to a text file for further analysis. However, the team member immediately ran into a problem and approached me.
Team member: I am facing an issue with the storage of comments in our database.
Me: What happened? Is the data not being stored as expected?
Team member: No, it appears that the storage is correct, but I am unable to view it in SSMS.
Me (stumped): Okay…..let’s take a look right away.
(We walk over to the team member’s desk to take a look at the issue).
Team member: Here’s the research I have done till now. As you can see, the LEN and DATALENGTH functions show the correct length of the comments entered, but when I execute a SELECT from the table with results in text mode (Ctrl + T), I only get 256 characters in text results mode of SSMS. Can you help me get the complete data?
USE tempdb; GO SET NOCOUNT ON; --Temporary table variable for demonstration of the issue DECLARE @textModeTest TABLE (RecId INT, RecText VARCHAR(MAX)); --Inserting sample data INSERT INTO @textModeTest (RecId, RecText) VALUES (1, REPLICATE('a',2000)); --Selecting the data lengths and associated data SELECT LEN(RecText) AS LENValue, DATALENGTH(RecText) AS DATALENGTHValue, RecText FROM @textModeTest; --Trying to get the length of the data selected --To do this, copy the output from the "RecText" column in the query above --and attempt to fetch the length for that SELECT LEN('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa') AS VisibleTextLength;
Me: Okay, now I understand your confusion. As a matter of fact, the SSMS is working as expected.
Team member: If it’s working as expected, how can I view the actual, i.e. non-truncated value of the column? Are there any alternatives?
Me: Sure! There are not one, but at least two ways in which you can view the non-truncated data. First I will show how to fix up SSMS, and will later share a method that I prefer over SSMS for these purposes.
Method 01: Changing the maximum number of characters displayed
A simple configuration change in the SSMS can fetch upto 8192 (8K) characters when the results are displayed in Text mode (Ctrl + T). To fix this in SSMS:
- Go to Tools –> Options
- Navigate out to Query Results –> SQL Server –> Results to Text
- Change the value under “Maximum number of characters displayed in each column” from 256 to 8192
- Restart SSMS
- Next time when the query is executed, one can notice that the SSMS displays upto 8192 characters in the related columns of the result set
Method 02: Using BCP
The method shown above has a major limitation around the number of characters being displayed – they are limited to 8192 characters. Instead of this, I generally use BCP/bulk-copy mechanisms to extract full-length data from the database.
More information about BCP and how to extract information into text files using BCP is available on my historical posts at:
- BCP & Bulk Insert
- Query Result configuration – Generate comma separated output without BCP or query changes
Me: So that’s it! Now you know not one but two methods of viewing and exporting full-length character data in SSMS or any other text file.
Team member: Thank-you so much! I never knew that SSMS was so flexible! A simply configuration change made my day!
Me: You are welcome! I am glad I could help.
Until we meet next time,