syscomments table does not allow more than 8060 chars

[RemoteSilicon]

Hello Everyone,

I am having quite a headache in trying to find a solution to this problem:

I have a database in SQL Server 2000 and have quite a few triggers on different tables. Problem is that when I try to update any record in some of these tables I get following error message displayed on the web page:

Warning: The table 'TABLE_NAME' has been created but its maximum row size (17275) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

Where the data I am inserting is well below the field limit and I know it's the syscomments table that is creating the problem. There is no error message when I delete these triggers.

Any ideas how to fix this.

Thanks!!!!

 

[SQLDenis]

a table in SQL server can not hold more than 8060 caracters (not includeing TEXT and Image datatypes)
Show the trigger code, are you creating any temp or permanent tables in the trigger?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

when you execute this

create table TestSize (col1 varchar(4000),col2 varchar(4000), col3 varchar(4000))


you will get this warning
Warning: The table 'TestSize' has been created but its maximum row size (12027) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

even though you might not fill up these columns you will still get the warning


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Catadmin]

I've had this problem before when modifying an existing table. I went to increase the size of one column and got that error, with SQL refusing to modify the column, because 1 record in the table caused an "Over 8060" issue. It was because SQL was evaluating the largest record in the table before it made the modification.

You should, however, make sure when creating your table, that you're creating it below or at the 8060 byte limit (preferably below). If you make it small, you can always modify the columns upwards later, but if you make it over the limit, the temptation to over-fill your table will always be there.

Also, overly large tables, whether or not you're using all the available space in a record, cause performance & overhead issues, so it's always good practice to make the table the smallest possible size instead of the biggest.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"