Maximum row size exceeded while copying varbinary (max) field
I need to transfer data (varbinary (max)) from one table to another. When doing an update for this, I get the following error:
Msg 511, Level 16, State 1, Line 18 Cannot create a row of size 8078 which is greater than the allowable maximum row size of 8060.
This is the update I used to copy from DocumentPublication table to DocumentVersion
UPDATE docver SET RecapRTF = CAST(RTFPublication as VARBINARY(MAX)) FROM DocumentVersion docver INNER JOIN DocumentPublication docpub ON docpub.IdDocumentVersion = DOCVER.id
or without broadcast
UPDATE docver SET RecapRTF = RTFPublication FROM DocumentVersion docver INNER JOIN DocumentPublication docpub ON docpub.IdDocumentVersion = DOCVER.id
While doing the update line by line, I have highlighted the line that is giving the error. The weird part is that the data in this field is only 3950 bytes, and other lines with less or more (like 2000 bytes or 20 MB) work fine.
Then I recreated the destination table with a different name and now it can copy the varbinary field!?!?
The SQL Server version is 2008 R2 with latest updates and database with 100 compatibility (sql server 2008). I ran DBCC CHECKDB and DBCC CHECKALLOC and found no errors.
Any clues as to what might be wrong here?
source to share