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.
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)
14 Comments. Leave new
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.
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(”)
we can also get this using select query in xml format,
SELECT * FROM emp for XML PATH;
This works so well for my needs. Thanks for posting it!
+1 Simple and easy. Got me out of trouble in 5 secs
we could try BCP to dat file instead of ssis wizard
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
Typo in your steps paragraph: it’s “right click”, not “write click”.
thanks a lot for this post. It clears my concept.
Enter your comment here…Pinal Dave’s solution covers everything (conversions, error from xml etc)
All other work on particular situation
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?
Some views have over 147000 bytes in the sys.sql_modules.definition. This approach is not working
Oh.. Did you find a solution?
Run the select query and get the output in result window, then right click > Save result As (CSV etc)