//
You're reading...
SQL

Retrieve more than 65535 characters from SQL varchar/nvarchar column

Few days ago, I was stuck in a problem to retrieving data from “varchar(max)” column. The data in the column was more than 30k characters. I realized that we can not retrieve more than 65535 characters from the column using this “regular” statement:

Select * from TableA  

See how it works in sql:

In order to retrieve values we need to use the XML statements. Using XML statements we can retrieve unlimited data from the column (See Tools->Options->Results->Grid-> “XML Data”).

The statement will look like:

Select * from tableA for xml path        -- Here we can only specify some column name as well.

Lets see the difference:

Now click on the result. It will show data in a separate tab under SQL. (If the result has large number of characters it may take few seconds to load)

Same will be the case if you have nvarchar(max) column.

I hope it is clear and it helps!

Awaited for your feedbacks and suggestions….!

Cheers!

Regards,

Shahan

Advertisements

About Shahan Ayyub

Shahan is a Microsoft Certified Professional of .Net. Mostly self taught programmer and a quick learner, who just love to code and always eager to find unique solutions to the common problems. Currently working as an iOS developer.

Discussion

2 thoughts on “Retrieve more than 65535 characters from SQL varchar/nvarchar column

  1. Nice Solution.

    Posted by Muhammad Imran | August 30, 2012, 7:14 pm

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

%d bloggers like this: