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)