Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2006
    Posts
    2

    Red face Unanswered: text truncated on INSERT (urgent)

    Hi,

    Question: Why is INSERT of a string having 8000+ characters causing truncation in a TEXT field?

    We are working on a website for a client, and one of their CMS-driven pages contains more than 8000 chars for its content. The database field was originally TEXT datatype at the default size, but the content was getting truncated upon INSERT. So I looked into why this was happening, tried many things including:

    1. Executing: EXEC sp_tableoption 'section', 'text in row', '7000'
    2. Changing datatype from TEXT to VARCHAR(8000). (of course this truncated at 8000).

    And this truncation still occurring. This is happening on SQL Server 2000 installed and configured on our client's server. The servers we host don't have this problem, and we have even tested the INSERT on our server and it does not truncate. So why is it truncating only on our client's Sql Server? Is there some kind of default configuration setting that limits the allowable length?

    I'm not intimately familiar with the large BLOB datatypes, and haven't really needed to deal with this issue before now. Now the client is upset about the limitation and does not wish to copy edit the content for their webpage. This is an urgent matter. I need to figure out the problem within a few hours today (it is morning in Europe on Tuesday 14 mar 2006 right now). Thanks in advance.

    UPDATE: I HAVE RESOLVED THE ISSUE. THE PROBLEM WAS A RESTRICTION IN THE STORED PROCEDURE INPUT VARIABLE DECLARATION -- WAS TRUNCATING AT 8000 CHARACTERS.
    Last edited by claustrophon; 03-14-06 at 11:34.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Use files to move the data rather than using SQL Script. My suggestion would be to use the Native format option of the BCP Utility, but there are other options too.

    -PatP

  3. #3
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    from BOL this may be you problem

    Maximum sizes/numbers
    Object SQL Server 7.0 SQL Server 2000

    Bytes per row 8,060 8,060

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by rbackmann
    from BOL this may be you problem

    Maximum sizes/numbers
    Object SQL Server 7.0 SQL Server 2000

    Bytes per row 8,060 8,060
    No, text data is not stored in the row, and so is not limited by the maximum record size limit.

    Quote Originally Posted by claustrophon
    The database field was originally TEXT datatype at the default size
    Huh? The text datatype has no default size, unless you are referring to the 16 bytes required for the pointer.

    claustrophon, please post the INSERT statement you are using, and indicate whether you are using direct SQL or updating the table through a stored procedure or view.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by rbackmann
    from BOL this may be you problem

    Maximum sizes/numbers
    Object SQL Server 7.0 SQL Server 2000

    Bytes per row 8,060 8,060
    Can't get you..??? There is no problem in storing 16000 or >32000 charaters in text datatype field.And while inserting ,its just working fine in my place.Plz provide the insert statement that u are using.

    Joydeep
    Last edited by rudra; 03-14-06 at 10:57.

  6. #6
    Join Date
    Mar 2006
    Posts
    2
    Hey guys, sorry for my stupidity. When you asked about the INSERT statement, I went to look at the stored procedure that performs the INSERT, and lo and behold, the stored proc limits the IN variable decalaration to a varchar(8000) when it should be just text. Sorry to waste your time, but I have fixed my issue! Thanks for your help, I love coming to this site, so much love!

  7. #7
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    Question
    If you have varchar fields are you limited to 8060
    I see that the text datatype allows a great number of characters

    If I have fields 1 - 8 at 1100 length varchar I don't think that I could fill these to capacity is this true?

    Thanks in advance for insight.

  8. #8
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by rbackmann
    Question
    If you have varchar fields are you limited to 8060
    I see that the text datatype allows a great number of characters

    If I have fields 1 - 8 at 1100 length varchar I don't think that I could fill these to capacity is this true?

    Thanks in advance for insight.
    No,varchar datatype has maximum limit of 8000.So you can't keep 8060 there.
    if you specify varchar(1100) ,then you can keep a maximum of 1100 characters. Is it clear to you?Better read BOL datatype .Link in the above post.
    Joydeep

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by rbackmann
    Question
    If you have varchar fields are you limited to 8060
    I see that the text datatype allows a great number of characters

    If I have fields 1 - 8 at 1100 length varchar I don't think that I could fill these to capacity is this true?

    Thanks in advance for insight.
    Any single varchar column may hold up to 8000 characters (or 4000 unicode), but the total size of the record cannot exceed 8060. Under some circumstances you my receive a warning about this from SQL Server when creating or altering your table, but the table will be created anyway.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    Thanks Blindman that was what I thought.

  11. #11
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by blindman
    Any single varchar column may hold up to 8000 characters (or 4000 unicode), but the total size of the record cannot exceed 8060. Under some circumstances you my receive a warning about this from SQL Server when creating or altering your table, but the table will be created anyway.
    Thanks Pat for your clarification.
    well, what about this one ,it states max size should be 8039 not 8060 .
    LINK
    Last edited by rudra; 03-14-06 at 22:34.

  12. #12
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506

    Max size 8039 ??

    Thanks Pat for your clarification.
    well, what about this one ,it states max size should be 8039 not 8060 .
    LINK
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm really surprised that the folks at SQL Server Central left that page there for so long. It is chock full of errors, of various kinds. I'm sure that Mr Jones meant well, but at least at the time that he wrote that page, he really didn't understand much about how the data gets stored on a SQL Server Page.

    Pages in the database can be used in many ways. There are blob pages (IMAGE and TEXT data types), index pages (which are a longish article in their own right), and data pages. No one page can have more than 8060 bytes of data on it, but that by NO means implies that every data page will have a full 8060 bytes of data on it.

    -PatP

  14. #14
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by Pat Phelan
    I'm really surprised that the folks at SQL Server Central left that page there for so long. It is chock full of errors, of various kinds. I'm sure that Mr Jones meant well, but at least at the time that he wrote that page, he really didn't understand much about how the data gets stored on a SQL Server Page.

    Pages in the database can be used in many ways. There are blob pages (IMAGE and TEXT data types), index pages (which are a longish article in their own right), and data pages. No one page can have more than 8060 bytes of data on it, but that by NO means implies that every data page will have a full 8060 bytes of data on it.

    -PatP
    Hi Pat,
    Hmm... really interesting . Now I have time to look at it.Let me see what Mr.Jones has wrote
    Last edited by rudra; 03-15-06 at 00:24.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •