Warning While Adding a New Field in Sql Server 2000 Database Table

Question

Dear Friends,
I am facing a Warning Message in sql server 2000, while Adding 2 Fields by using
Alter Query.
My Query:
1)alter table Receipt_Detail_New
add BillingName varchar(100)

it shows me the Following Message:Warning The table 'Receipt_Detail_New' has been created but its maximum row size (9886) 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.

2)alter table RECEIPT_PAYMENT
add BillingName varchar(100)

it shows me the Following Message:Warning: The table 'RECEIPT_PAYMENT' has been created but its maximum row size (11121) 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.

what could be done in such Cases
Please Reply.

Thanks & Regards,
Aamir

Aamir Khan · Answer

Dear Vulpes & Senthil..thanks For Ur repliess

Senthilkumar · Answer

Hi Aamir, I do not think, you have any solution to fix this in sql server 2000. But you can overcome in 2005 by set the maximum length to the columns. All you need to do is suppress the column size by setting less than 8060 bytes. For related info: http://www.mssqltips.com/sqlservertip/2242/row-sizes-exceeding-8060-bytes-in-sql-2005/

Vulpes · Answer

It appears that the maximum row size was well above the limit of 8060 bytes before you even added these two new fields. The chances are that there's a very large field already in the table which is upsetting matters. If that's the case, I'd see whether you could reduce its size to bring the overall length back within the 8060 bytes limit.

Aamir Khan · Answer

Hi Senthil, Is there any Solution on it.

Senthilkumar · Answer

Hi, The reason, sql server provides limit to the each row in sql server table. Here it is calculated based on the size you defined each column. But while inserting data into the rows, if size may be less or more. then you will get actual error.