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?

+3


source to share


2 answers


The table docver

may have a lost or modified column still consuming space.



See Why do I need to rebuild the table after deleting or adding a column?

+4


source


It's just that you don't have to look for it ...



ALTER TABLE [docver] REBUILD

      

+2


source







All Articles