I have searched for a couple of hours but cannot find the exact solution for this.

In my SQL Server 2005 database table (tableA) there is a varchar(max) column.

I want to get complete value of that column (now it is not displaying any value) in SQL Server Management Studio.

Can anybody provide me a solution?

  • How are you trying to view the value now to know it is not displaying any value now?
    – user507
    Jul 25, 2014 at 5:14
  • I have tried querying the table. used select * statement Jul 25, 2014 at 5:16
  • Then if that does not return any values there aren't any there. Unless you do not have permission to that column, possibly.
    – user507
    Jul 25, 2014 at 5:18
  • @ShawnMelton- when I view same database table from SSMS 2008, the value is there. I think there must be configuration to be set in SSMS 2005 to view lenghty text in varchar max field. because if the text in varchar max field is not lengthy then I can view the 2005 as well Jul 26, 2014 at 3:46
  • That is a tidbit of information that would have helped solve your problem much sooner...
    – user507
    Jul 27, 2014 at 4:34

1 Answer 1


If the issue you are having is Truncation you can use this XML approach that gets around most of the issues with XML entitisation.

declare @VeryLongText nvarchar(max) = '';

SELECT top 100 @VeryLongText = @VeryLongText + '

' + OBJECT_DEFINITION(object_id) 
FROM sys.all_objects 
WHERE type='P' and is_ms_shipped=1

SELECT LEN(@VeryLongText)

SELECT @VeryLongText AS [processing-instruction(x)] FOR XML PATH('')

PRINT @VeryLongText /*WILL be truncated*/

Make sure that the "XML data" limit in SSMS is set sufficiently high!


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.