May 14, 2004 at 8:38 am
when I execute a stored procedure in SQL SERVER 7.0 , Desktop Edition, I received this eror:
Server: Msg 511, Level 16, State 2, Line 1
Cannot create a row of size 8171 which is greater than the allowable maximum of 8060.
The same procedure is executed without errors on SQL SERVER 7.0 Desktop Edition on other PC.
How can I solve the problem?
May 17, 2004 at 8:00 am
This was removed by the editor as SPAM
September 13, 2004 at 5:21 pm
I have the same problem
September 16, 2004 at 10:06 pm
You probably didn't get much interest here, because this forum is for SQL Server 2005 (aka Yukon)!
But it is true that the row size in SQL Server is limited to 8060 bytes. There are at least two common ways to work around this problem.
The first way is to change larger fields (if there are any) into their "large" equivalents, which for SQL 7 means text, ntext, or image. These datatypes store only a 16-byte locator inline (read: "in the data row") and the actual data is stored elsewhere. Therefore, even though these datatypes can accept large amounts of data, they only cost 16 bytes against your 8060 byte "budget."
The other approach is to split your table into two (or more) tables that have the same primary key. Because the primary keys are the same, there will also be a 1-to-1 relationship between these tables. One place I worked once (a Fortune 100 company with one of the 10-highest revenue generating sites on the "legal" Internet, for what it's worth) split tables this way, and was actually able to increase performance at the same time, by putting the commonly accessed columns into one table, and the less-frequently accessed columns into another. So, for inventory information, we might have one table called inventory_header, and another called inventory_header_extension, or something like that. The two tables together would really map to a single entity in E-R terms, but to reconstitute this entity we would have to look at both tables simultaneously. It seemed like an unfortunate thing to have to do, but it is necessary sometimes. Unlike some other database systems, SQL Server does not allow rows to span multiple pages, and 8K is the pages size (and there is 132 bytes set aside inside that page for page management purposes - header and internal directory info, for example).
Hope this helps,
Chris
September 17, 2004 at 1:11 pm
Do you have the same service pack on each machine? You can run 'select @@version' to compare.
Short of a very specific scenario, its bad stuff to create a table that exceeds the max width, or in my opinion even comes close to it. To get max performance you want to get as many rows on a page. Given a page size of 8k, a max size record could end up causing you page reads equal to rows read. Not great at all for performance. Using text columns is one good idea for reducing the width.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply