SQL SERVER – Retrieve All the Data from VARCHAR(MAX) Column

In this blog post we will discuss a very common problem which developer who are working with SQL Server Management Studio and VARCHAR(MAX) Datatype faces. In database table if you have over 8200 characters in a column with datatype VARCHAR (MAX) it is not easy to display them in SSMS. Even though we try to do that, there is a limit of how much data we can display in SSMS resultset window.

However, If your business need is that you need to retrieve VARCHAR (MAX) datatype column in SSMS, you can use following trick where you can export the data to text file. To do that, here are the steps:

Go to Database >> Write Click on Tasks >> Click on Export Data

It will bring up with a Wizard. Select the source as a current database and select output file as a flat file. Now select the option to Write a query and follow the next steps of wizards.

Here is the visual explanation of the entire process.

Let me know if there are methods which we can use to achieve the same task. I will write a blog post with due credit.

Reference: Pinal Dave (http://blog.sqlauthority.com)

8 thoughts on “SQL SERVER – Retrieve All the Data from VARCHAR(MAX) Column

  1. one possible way to retrieve all characters from varchar(max) in SSMS is setting the ‘Maximum number of characters displayed in each column’ property which you will find in Options\Query Results\SQL Server\Results to Text. Default value is set to 256 char. You will need to increase by yourself.

    Like

  2. converting to XML works pretty well for this as well:

    declare @VeryLongText nvarchar(max) = ‘long text…';

    select @VeryLongText

    SELECT @VeryLongText AS LongText FOR XML PATH(”)

    Like

  3. What about using PowerShell from SSMS: invoke-sqlcmd -query “exec master.dbo.sp_databases” -database master -serverinstance localhost | export-csv -path c:\temp\test.csv -NoTypeInformation

    Like

  4. Enter your comment here…Pinal Dave’s solution covers everything (conversions, error from xml etc)
    All other work on particular situation

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s