Text Column Limited to 65k?

Posted on 2009-02-24
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

    see this,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

    also this

    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

    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.
    LVL 12

    Accepted Solution

    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

    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
    >>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
    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

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
    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…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now