September 12, 2008 at 5:04 am
As a side effect of using a SP_MSFOREACHTABLE Im seeing the following on a few of my tables
Warning: The table 'tCompany' has been created but its maximum row size (10757) 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.
Does this mean each and every insert or update to this table will fail ? Or does it refer only to rows above the page size ..
Can I idenitify which rows excede 8060 ?
thanks si
September 12, 2008 at 5:59 am
Simon_L (9/12/2008)
Does this mean each and every insert or update to this table will fail ? Or does it refer only to rows above the page size ..
Can I idenitify which rows excede 8060 ?
No it does not mean that all inserts will fail and the number of rows which exceeod is zero, because such a row is not possible.
What happened is that you have some tables where the total maximum length if all variable length columns are field to the max, would be longer than 8060 bytes. Only in that case an insert would fail.
Here's a simple example:
CREATE TABLE #test
(c1 int, c2 varchar(4000) ,c3 varchar(5000))
Warning: The table '#test' has been created but its maximum row size (9029) 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.
-- This insert works
INSERT INTO #test
SELECT 1, 'This is a Test', 'Still testing'
(1 row(s) affected)
-- This Insert fails
INSERT INTO #test
SELECT 2, REPLICATE('A',4000), REPLICATE('B',4500)
Msg 511, Level 16, State 0, Line 2
Cannot create a row of size 8517 which is greater than the allowable maximum of 8060.
The statement has been terminated.
[font="Verdana"]Markus Bohse[/font]
September 12, 2008 at 6:01 am
that explains it wonderfully .. thank you
~simon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply