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 >> Right 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.

SQL SERVER - Retrieve All the Data from VARCHAR(MAX) Column 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

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 (https://blog.sqlauthority.com)

Previous Post
MySQL – Learning Online MySQL Workbench Guided Tour – Pluralsight Course
Next Post
SQL SERVER – Learning SSIS – Where Do I Start? – Notes from the Field #014

Related Posts

No results found.

14 Comments. Leave new

  • Christian Lorber
    February 5, 2014 3:49 pm

    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.

    Reply
  • 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(”)

    Reply
  • Sanjay Monpara
    February 6, 2014 2:48 pm

    we can also get this using select query in xml format,

    SELECT * FROM emp for XML PATH;

    Reply
  • we could try BCP to dat file instead of ssis wizard

    Reply
  • What about using PowerShell from SSMS: invoke-sqlcmd -query “exec master.dbo.sp_databases” -database master -serverinstance localhost | export-csv -path c:temptest.csv -NoTypeInformation

    Reply
  • Typo in your steps paragraph: it’s “right click”, not “write click”.

    Reply
  • thanks a lot for this post. It clears my concept.

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

    Reply
  • 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?

    Reply
  • Some views have over 147000 bytes in the sys.sql_modules.definition. This approach is not working

    Reply
  • Vignesh Vaidyanathan
    October 1, 2020 6:34 pm

    Run the select query and get the output in result window, then right click > Save result As (CSV etc)

    Reply

Leave a Reply