[Last Call] Learn how to a build a cloud-first strategyRegister Now


Text Column Limited to 65k?

Posted on 2009-02-24
Medium Priority
Last Modified: 2012-05-06
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.

Question by:tradeline
LVL 12

Expert Comment

ID: 23726276
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.
LVL 12

Expert Comment

ID: 23726295
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.

Author Comment

ID: 23726347
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.
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

LVL 12

Accepted Solution

Dimitris earned 500 total points
ID: 23726691
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.
LVL 39

Assisted Solution

BrandonGalderisi earned 500 total points
ID: 23726774
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.
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1000 total points
ID: 23730214
>>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.
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1000 total points
ID: 23730231
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 :)

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question