Updated or inserted row is bigger than maximum siz

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

SQL Server 6.5 \ SQL Server 7.0

Updated or inserted row is bigger than maximum siz

Author

Topic

Spiderman
Starting Member

5 Posts

Posted - 2008-04-18 : 02:22:48

I have encountered the following error in SQL 6.5. Can anyone tell me why its occuring and how to fix it?

Updated or inserted row is bigger than maximum size () allowed for this table

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-18 : 02:27:15

Just like the error says, you are trying to either update or insert a row that is bigger than what the table allows.

Could you post the DDL for the table and the query that is erroring?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Spiderman
Starting Member

5 Posts

Posted - 2008-04-18 : 02:59:35

Unfortunately not sure what the query is. But I can get the DDL if you can tell me how to get what you require.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-04-18 : 13:48:51

The table layout definition. In EM you should be able to right-click on the table and select generate script or sp_help tablename in QA. Also, post the string that's giving you problems inserting as well.

Terry

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-19 : 16:41:03

Sql6.5 has 4000 bytes row size limit, ensure sum of columns length doesn't excess that limit.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-19 : 17:52:20

Wasn't it 2048 in v6.5

The problem is that your table has variable legth columns and the sum of the max sizes for all columns is greater than 2048 bytes. Not a problem if the total for the data is less than 2048 but once you try to update or inset a row greater than this size you will get the error.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-19 : 20:00:49

Maybe, can't remember since didn't use it for years.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-19 : 20:16:38

I can't either but think that was the page size - row size may be less.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Spiderman
Starting Member

5 Posts

Posted - 2008-04-20 : 19:35:43

/****** Object: Table dbo.ALL_SCHOOLS_CONTACTS Script Date: 21/04/2008 9:30:11 AM ******/
CREATE TABLE dbo.ALL_SCHOOLS_CONTACTS (
SCHOOL_NO int NOT NULL ,
SCHOOL_PHONE_STD_NO varchar (6) NULL ,
SCHOOL_PHONE_NO varchar (15) NULL ,
PRINCIPAL_TITLE varchar (4) NULL ,
PRINCIPAL_1ST_NAME varchar (15) NULL ,
PRINCIPAL_SURNAME varchar (20) NULL ,
PRINCIPAL_HOME_STD_NO varchar (6) NULL ,
PRINCIPAL_HOME_PHONE_NO varchar (15) NULL ,
PRINCIPAL_MOBILE_PHONE_NO varchar (15) NULL ,
ACT_PRINCIPAL_TITLE varchar (4) NULL ,
ACT_PRINCIPAL_1ST_NAME varchar (15) NULL ,
ACT_PRINCIPAL_SURNAME varchar (20) NULL ,
ACT_PRINCIPAL_HOME_STD_NO varchar (6) NULL ,
ACT_PRINCIPAL_HOME_PHONE_NO varchar (15) NULL ,
ACT_PRINCIPAL_MOBILE_PHONE_NO varchar (15) NULL ,
SECD_IN_CHARGE_TITLE varchar (4) NULL ,
SECD_IN_CHARGE_1ST_NAME varchar (15) NULL ,
SECD_IN_CHARGE_SURNAME varchar (20) NULL ,
SECD_IN_CHARGE_HOME_STD_NO varchar (6) NULL ,
SECD_IN_CHARGE_HOME_PHONE_NO varchar (15) NULL ,
SECD_IN_CHARGE_MOBILE_PHONE_NO varchar (15) NULL ,
OIC_ADMIN_TITLE varchar (4) NULL ,
OIC_ADMIN_1ST_NAME varchar (15) NULL ,
OIC_ADMIN_SURNAME varchar (20) NULL ,
OIC_ADMIN_HOME_STD_NO varchar (6) NULL ,
OIC_ADMIN_HOME_PHONE_NO varchar (15) NULL ,
OIC_ADMIN_MOBILE_PHONE_NO varchar (15) NULL ,
COUNCIL_PRES_TITLE varchar (4) NULL ,
COUNCIL_PRES_1ST_NAME varchar (15) NULL ,
COUNCIL_PRES_SURNAME varchar (20) NULL ,
COUNCIL_PRES_SCHOOL_STD_NO varchar (6) NULL ,
COUNCIL_PRES_SCHOOL_PHONE_NO varchar (15) NULL ,
COUNCIL_PRES_WORK_STD_NO varchar (6) NULL ,
COUNCIL_PRES_WORK_PHONE_NO varchar (15) NULL ,
COUNCIL_PRES_HOME_STD_NO varchar (6) NULL ,
COUNCIL_PRES_HOME_PHONE_NO varchar (15) NULL ,
COUNCIL_PRES_MOBILE_PHONE_NO varchar (15) NULL ,
VACATN_CONTACT_TITLE varchar (4) NULL ,
VACATN_CONTACT_1ST_NAME varchar (15) NULL ,
VACATN_CONTACT_SURNAME varchar (20) NULL ,
VACATN_CONTACT_SCHOOL_STD_NO varchar (6) NULL ,
VACATN_CONTACT_SCHOOL_PHONE_NO varchar (15) NULL ,
VACATN_CONTACT_WORK_STD_NO varchar (6) NULL ,
VACATN_CONTACT_WORK_PHONE_NO varchar (15) NULL ,
VACATN_CONTACT_HOME_STD_NO varchar (6) NULL ,
VACATN_CONTACT_HOME_PHONE_NO varchar (15) NULL ,
VACATN_CONTACT_MOBILE_PHONE_NO varchar (15) NULL ,
EMERG_CONTACT_TITLE varchar (4) NULL ,
EMERG_CONTACT_1ST_NAME varchar (15) NULL ,
EMERG_CONTACT_SURNAME varchar (20) NULL ,
EMERG_CONTACT_HOME_STD_NO varchar (6) NULL ,
EMERG_CONTACT_HOME_PHONE_NO varchar (15) NULL ,
EMERG_CONTACT_MOBILE_PHONE_NO varchar (15) NULL
)
GO

The problem I have is I can't see the line that is erroring as I don't have access to the box that its occuring on. I only get the message.

The error says that the maximum size is 1591 bytes which is a confusing number.

Just wondering if this is a code related issue or a database related issue.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-04-21 : 15:12:04

SQL2K doesn't return any errors with the layout noted above (maybe a problem in SQL 7?). What about the input string, can you provide a sample of what's failing??

Terry

Spiderman
Starting Member

5 Posts

Posted - 2008-04-21 : 19:55:30

Thats my problem. I have created a program which runs on a SQL 6.5 box. I don't have access to that box so i'm not sure what exactly causes the error. I only get the error my program spits out.

I have setup a SQL 6.5 server here and run the program and had no error. Thats why I'm wondering if its a database setup error. Eg. The below shouldn't be 1591 bytes.

Updated or inserted row is bigger than maximum size (1591 bytes) allowed for this table.

OR is it a problem with my program?

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-04-22 : 16:30:55

Post the input strings. What I've seen so far is fine, I can create the table without issues.

Terry

Spiderman
Starting Member

5 Posts

Posted - 2008-04-24 : 00:33:31

We attempted to run it today and it worked fine! Someone was obviously screwing with the DB. Would love to show you the input string but I don't have it! Only the error. Don't have access to the database that errored and its no longer doing the error.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-04-25 : 15:46:14

Going to make it real tough for debugging! You should be provided with the data that you are testing (?? I'm assuming, testing!). Otherwise, whenever IT doesn't work, it's going to be your fault. I set limits as opposed to what developers can do on a production box but always provide plenty of test data to be as thorough as possible before an application is promoted to production. If I were you and they won't/can't change internal procedures, I'd be running for the nearest exit!

Terry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources