复制 varbinary(max) 字段时超出了最大行大小 [英] maximum row size exceeded when copying varbinary(max) field

问题描述

我需要将数据 (varbinary(max)) 从一张表迁移到另一张表.执行更新以执行此操作时，出现以下错误

I need to migrate data (varbinary(max)) from one table to another. When executing an update to do 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.

这是我用来从表 DocumentPublication 复制到 DocumentVersion 的更新

This is the update I've used to copy from table DocumentPublication to DocumentVersion

UPDATE docver SET RecapRTF = CAST(RTFPublication as VARBINARY(MAX)) FROM
DocumentVersion docver INNER JOIN DocumentPublication docpub 
ON docpub.IdDocumentVersion = DOCVER.id

或不带演员

UPDATE docver SET RecapRTF = RTFPublication FROM
DocumentVersion docver INNER JOIN DocumentPublication docpub  
ON docpub.IdDocumentVersion = DOCVER.id

通过逐行执行更新，我隔离了出现错误的行.奇怪的是，这个字段中的数据只有 3950 字节，其他行更少或更多(例如 2000 字节或 20MB)工作正常.

By executing the update row by row I've isolated the row that gives the error. The strange part is that the data in this field is only 3950 bytes and other rows with less or more (e.g. 2000 bytes or 20MB) work fine.

然后我以不同的名称重新创建了目标表，现在它可以复制 varbinary 字段了！?!?

I've then recreated the destination table under a different name and now it can copy the varbinary field!?!?

SQL Server 版本为 2008 R2，具有最新更新和兼容性 100 (sql server 2008) 的数据库.我已经运行了 DBCC CHECKDB 和 DBCC CHECKALLOC 并且没有发现错误.

SQL Server version is 2008 R2 with lastest updates and database with compatibility 100 (sql server 2008). I've run DBCC CHECKDB and DBCC CHECKALLOC and the did not find errors.

这里有什么问题的线索吗?

Any clues to what could be wrong here?

推荐答案

您的 docver 表可能有删除或更改的列，但仍占用空间.

Your docver table probably has a dropped or altered column still consuming space.

参见 我为什么要重建一个删除或添加列后的表?

这篇关于复制 varbinary(max) 字段时超出了最大行大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！