Cannot create a row of size 8252 which is greater than the allowable maximum of 8060.

Question

Post reply

Cannot create a row of size 8252 which is greater than the allowable maximum of 8060.

Chrisb-529708

SSC Enthusiast

Points: 177
More actions
October 31, 2007 at 3:37 am

#153927

I need to update a column of size 4000 with datalength column data of size 3730.
The total size of the record before updating this column is 749 bytes.
Still I receive the message :
begin tran
update a
set a.nvcEmailContent = b.nvcEmailContent
from dtlActivity a inner join tmp_dtlActivity b on (a.uidActivityID = b.uidActivityID)
where b.uidActivityID = 'F472731B-CBB7-48C3-8BE4-E4B726EC92A8'
------------------------*/
Msg 511, Level 16, State 1, Line 2
Cannot create a row of size 8252 which is greater than the allowable maximum of 8060.
The statement has been terminated.
(0 row(s) affected)

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

Sounds like your column(s) is defined as a nvarchar which takes two bytes of storage for each character.

Markus

[font="Verdana"]Markus Bohse[/font]

Chrisb-529708 SSC Enthusiast Points: 177 More actions · Answer 2

I used the function datalength and not len to calculate the total space used and as far as I know, this function take the 2bytes per character into account - not sure???. Keep in mind that this value was stored into a nvarchar(2000) field and I increase it to nvarchar(4000). Also, this change was done with merge replication in mind.:cool:

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

It might be the case that the total length of the entire row (including columns other than there in update) is exceeding the limit.

Well, in this case you can change the data type from nvarchar(4000) to nvarchar(max)...

--Ramesh

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 4

Not really sure what is going on but you're right the datalength function should return the actual bytelength.

But you also mention the use of merge replication. I'm know for a fact that in SQL 2000 then you're using merge replication the actual record length would be limited to about 6000 ytes instead of the standard 8060. The reason for this are the conflict tables which get created. These are exact copies of the replicated tables plus some additional columns which use about 2000 bytes. Not sure if this limitation is still there in SQL 2005, but maybe that's you're problem.

Markus

[font="Verdana"]Markus Bohse[/font]