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.

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

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

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

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

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

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

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

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

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

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

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)

9 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

  5. Hi , I am getting an error saying
    ‘Executing (Error)
    Messages
    Error 0xc02020c5: Data Flow Task 1: Data conversion failed while converting column “BusinessContactnamesUsers” (32) to column “BusinessContactnamesUsers” (115). The conversion returned status value 4 and status text “Text was truncated or one or more characters had no match in the target code page.”.
    (SQL Server Import and Export Wizard)

    Error 0xc020902a: Data Flow Task 1: The “Data Conversion 0 – 0.Outputs[Data Conversion Output].Columns[BusinessContactnamesUsers]” failed because truncation occurred, and the truncation row disposition on “Data Conversion 0 – 0.Outputs[Data Conversion Output].Columns[BusinessContactnamesUsers]” specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
    (SQL Server Import and Export Wizard)

    Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “Data Conversion 0 – 0” (103) failed with error code 0xC020902A while processing input “Data Conversion Input” (104). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
    (SQL Server Import and Export Wizard)


    the ones are the column names whose datatype is nvarchar(max).
    Can you please guide?

    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