Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102

    Unanswered: error 'Cannot create a row of size xxxx' - my fix doesn't work

    Been doing some research after getting this error:
    Microsoft OLE DB Provider for SQL Server error '80040e14'

    Cannot create a row of size 8297 which is greater than the allowable maximum of 8060.
    And realised that my nice responsive varchars had a maximum total size. so I changed them for 'slower' text data types. but my DB still won't allow any more input.

    has the limit been reached now regardless of what I change or can I rebuild the DB to recover the space or something?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    First, what version are you on? 2000 or 2005?

    Second, post the DDL for your tables with the 8000 byte columns.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    That message is only a warning. The table is still created, I believe.

    EDIT: Unless you are actually trying to stuff that much data in a row. Then it becomes an error.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by MCrowley
    That message is only a warning. The table is still created, I believe.

    EDIT: Unless you are actually trying to stuff that much data in a row. Then it becomes an error.

    True enough...but it can't be a good design
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    well you get no prizes for guessing it's not the best designed DB in the world, I basically said that at the top. But it has been going fine for two years delivering words to a huge web site, right up to the point where it filled up.
    so I changed some data types from varchar to text in the hope of freeing up some space but it still seems to be full.
    so back to my Q: is it possible that I need to do something else to free up the space or is it something els?
    I think the DB is 2000 (2005 doesn't suffer from this same varchar limit does it?)
    here's the DDL - could well be all wrong, but that's why I'm here.
    [id] int IDENTITY(1, 1) NOT NULL,
    [dest_url] varchar(32),
    [country] varchar(32),
    [continent] varchar(16),
    [intro] varchar(2048),
    [quote1] varchar(512),
    [title1] varchar(512),
    [body1] varchar(2048),
    [quote2] varchar(512),
    [title2] varchar(512),
    [body2] varchar(2048),
    [quote3] varchar(512),
    [title3] varchar(64),
    [body3] text,
    [quote4] varchar(512),
    [title4] varchar(64),
    [body4] text,
    [quote5] text,
    [title5] varchar(64),
    [body5] varchar(2048),
    [quote6] varchar(512),
    [image1] varchar(255),
    [image2] varchar(255),
    [image3] varchar(255),
    [image4] varchar(255),
    [image5] varchar(255),
    [image6] varchar(255),
    [submissionDate] smalldatetime,
    [altTag1] varchar(255),
    [altTag2] varchar(255),
    [altTag3] varchar(255),
    [altTag4] varchar(255),
    [altTag5] varchar(255),
    [altTag6] varchar(255),
    I understood that text data types are slow and varchar is fast and only uses the space it needs but I suspect that is wrong.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Holy denormalization, Batman!

    Yeah, that is a mess and really needs to be normalized into subtables.
    Let me guess...you can't change the schema, right?

    Or you could upgrade to 2005 and use the new varchar(max) datatype.
    But let me guess...upgrading is not an option right now, right?

    The maximum data size of a record cannot exceed 8000 bytes (give or take). Replacing some of your columns with text datatypes should have resolved the issue for you, as the text pointers are only 16 bytes.

    Post the DDL for your table after you changed the datatypes.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    So that's considered a big ugly table then? you live and learn, I thought SQL Server was immensely powerful and nothing I chucked at it would give it a problem
    When I 'designed' it, it seemed like all the columns were not duplicated or needed anywhere else so should all go into one table.
    I don't really know about normalisation, can it be done retrospectively (and link internally) or will I have to change all my ASP code to find all the different tables?
    In any case, back to the original problem, is there a 'rebuild' option somewhere to reclaim empty space?
    Seems my DB is V7! could be upgraded to 2000 (oooo wow, thanks a bunch Mr. host)
    Last edited by darkmunk; 07-13-07 at 10:59.

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    you seem to have missed this crucial request of blindman's:

    "Post the DDL for your table after you changed the datatypes."

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That table should really be three or 4 tables

    Anytime you see a column with a name that ends in an incrementing #, thos solumns should be 1 column in a child table...not only is it a better design, it provides you with more flexibility in case you need more than n rows

    You basically are making rows into columns in your design

    So I guess, I guessed right
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    Thanks Brett, you guessed right. I know nothing.
    OK so normalising my table will also free up space because the records will be shorter, is that right?
    But this table only holds data on a country page and the incrementing numbers just tell the page where to put the block of text. It seems massive overkill (in itself) to create a table for each column (if it has a #), it's basically unique data which isn't called up anywhere else. and wouldn't I have to do complex joins etc or at least new sql strings to create the entire page.
    I don't really want to go back and rewrite the CMS code and it just seems wrong when I have a very simple database that just happens to have about 5000 chars of text for some countries.

    Isn't this a fairly crappy limitation for an expensive 'enteprise level database' ?

    I didn't repost my DDL because the first one WAS the new layout with the text fields put in to try and free up some space. They used to be varchar(2048).

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Splendid intro to database design & normalisation. I plan to work my way through his site myself.
    Problem you are having is specificly with first normal form.
    http://www.tonymarston.net/php-mysql...se-design.html

    Also - even though you have introduced text if you total up all the VARCHARS you still well exceed 8000 bytes.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well, rewriting code would suck...BUT...you could normalize the data, THEN create a view that looks like your existing table, and name the view what the name of the existing table is...no rewrite needed

    This is how I would have designed this...without really knowing what the app does

    Code:
    CREATE TABLE MAIN (
    [Mainid] int IDENTITY(1, 1) NOT NULL,
    [dest_url] varchar(32),
    [country] varchar(32),
    [continent] varchar(16),
    [intro] varchar(2048),
    [submissionDate] smalldatetime,
    )
    
    CREATE TABLE Body (
    [BodyId] int,
    [Mainid] int, 
    [quote] varchar(512),
    [title] varchar(512),
    [body] varchar(2048)
    )
    
    CREATE TABLE Images (
    [ImageId] int,
    [Mainid] int, 
    [image] varchar(255)
    )
    
    CREATE TABLE Tags (
    [TagId] int,
    [Mainid] int, 
    [altTag] varchar(255)
    )
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OH, and just because there's an 8k limit is a physical limitation at the time because of the page size...they removed this in 2005

    Your easiest path to a soultion really is an upgrade to 2k5

    BUT..this is a very good example of how important data modeling is before app development
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    excellent resource thanks.
    but then the answer to my Q is yes I will need to rewrite my SQL to join all the split tables.
    and No, it isn't really necessary to split up my table because the data is unique and only inputted once.
    and yes, SQL Server 7 sucks and I need a new DB host.
    And Yes, I can be a bit slow - I now see what is meant by the various limits and how all my varchars have stacked up.
    ta

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by darkmunk
    OK so normalising my table will also free up space because the records will be shorter, is that right?
    Normalising will not free up space. In fact, it will likely require more "space", but the size of each individual record will be reduced and thus you will not be hitting the 8k data limit per row.
    Quote Originally Posted by darkmunk
    It seems massive overkill (in itself) to create a table for each column (if it has a #),
    You should create a subtable to hold the Quote, Title, and Body data.
    You should create another subtable to hold image data.
    You should create another subtable to hold altTag data.
    If image and tag data are related, both should be stored in the same table.
    What you call overkill, we call good design.
    Quote Originally Posted by darkmunk
    I don't really want to go back and rewrite the CMS code and it just seems wrong when I have a very simple database that just happens to have about 5000 chars of text for some countries.
    Lesson learned: design the database first. Write the application code second.
    Quote Originally Posted by darkmunk
    Isn't this a fairly crappy limitation for an expensive 'enteprise level database' ?
    Sorry, but your ignorance of the product is not a reflection upon its quality.
    Quote Originally Posted by darkmunk
    I didn't repost my DDL because the first one WAS the new layout with the text fields put in to try and free up some space. They used to be varchar(2048).
    So change the remaining varchar(2048) columns to text. The point is, you should be able to add up the maximum sizes of all your data columns and stay under 8k.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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