Text Column Limited to 65k?

I have a strange bug:

On my development machine (sqlexpress) I have a text column that is storing a long text string (~100k) into the column.  Everything works fine.  However, when I deploy to production (sql server 2005 standard) all the text fields are truncated at 65k.  The same is true of varchar(Max).

Not sure what is causing this.

Who is Participating?
DimitrisSenior Solution ArchitectCommented:
You have a problem in the select on the query analyzer or when you use ADO or SQLClient?
The Query analyzer has a limit but the select through ADO or SQL Client should retrieve the data correctly.
DimitrisSenior Solution ArchitectCommented:
see this http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1228494,00.html#

as the article notes :
The text data type is used to store huge amounts of data. One field can store up to 2GB (231  1 bytes) of information. Only a 16-byte pointer to this data is stored in the table. Therefore, additional processing overhead is involved with the use of text columns. There are special functions for processing text values.
DimitrisSenior Solution ArchitectCommented:
also this http://blogs.msdn.com/msdnts/archive/2006/12/01/row-size-limitation-in-sql-2000-and-2005.aspx

In SQL 2005, VarChar(MAX), NVarChar(MAX) and VarBinary(MAX) allow storage of data up to 2 gigabytes. Image/Text data type in SQL 2000/2005 is also stored separately and will not be covered in 8KB size.
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

tradelineAuthor Commented:
Right, so back to my question.

If text/varchar(max) have no limitation affecting 100k worth of data, why is it that when I select the value of that text column I only see 65k worth of data?

I read that the select statements are also limited, but even after executing "SET TEXTSIZE 2147483647" I still only receive 65k worth of data from that column.

I'm not sure if the data is being truncated on the way in, or truncated by the select statement.

Based on the 2GB limits (and the fact that it's not throwing any exceptions), I'm assuming a limitation of the select statement??

Not sure how to resolve the problem though.
Your problem is a limitation of the tool you are using to query (SSMS - SQL Server Management Studio) and NOT of the data type.  SSMS is really not meant to be a tool used to query large amounts of data.  It can do it (to a point) but I guess M$ thoughts are that no one REALLY wants to look through 65K characters for what they are looking for.
Anthony PerkinsCommented:
>>all the text fields are truncated at 65k.<<
Correct, that is the maximum SSMS will display.  Consider yourself lucky SQL Server 2000 only allowed 8K.
Anthony PerkinsCommented:
P.S.  Correction: It is not "65K", but rather 64KB = 65,535 for Non Xml data and 2MB for Xml data.  So try converting it to Xml and you should be able to see more :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.