Im using Microsoft SQL Server Management Studio Express to connect to our SQL Server 2005 (think its 2005, its version 9.000 something something). I have a table with one column that saves a lot of text. I have set the column type to text. When i have a row with lots of text in this column i cannot delete it. I get the message "string or binary data would be truncated" when i try to delete it. If i try to edit the row i get the same message. What do i do?
我使用Microsoft SQL Server Management Studio Express连接到我们的SQL Server 2005(想想2005年的版本，它的版本是9000)。我有一个表，其中一个列可以保存很多文本。我已经将列类型设置为text。当这个列中有一行有很多文本时，我不能删除它。当我试图删除它时，会得到消息“字符串或二进制数据将被截断”。如果我尝试编辑行，我将得到相同的消息。我该怎么做?
Considerable : i fix it by drop table and create it again, but i wanna a Solution!
可观的:我通过删除表修复它并再次创建它，但是我想要一个解决方案!
5 个解决方案
#1
4
Normally this kind of error happens when you're inserting a long value into the column that can't store it. Check whether you don't have any triggers or any related logic that could do that on your behalf when you're deleting the row (e.g. for logging purposes / audit trail)
通常，当您将一个长值插入无法存储它的列时，会发生这种错误。检查在删除行时是否没有任何触发器或任何相关逻辑可以代表您执行此操作(例如，用于日志记录或审计跟踪)
#2
24
Though I'm late to the party, when I review the original poster's description of the problem, it sounds like they are trying to delete the row from within the table editor interface. I just encountered this same issue with a table containing a "text" column with long text, and it appears that the issue may in fact be tied to a limitation in the editor itself. From my own investigation, it appears that you will not be able to edit a row in which a text column exceeds 4000 characters. I tested with SSMS 2008 R2.
虽然我已经迟到了，但当我查看原始的海报对问题的描述时，听起来好像他们试图从表编辑器界面中删除这一行。我刚刚遇到了一个包含“文本”列和长文本的表的问题，这个问题实际上可能与编辑器本身的限制有关。根据我自己的调查，似乎您无法编辑一个文本列超过4000个字符的行。我用SSMS 2008 R2进行了测试。
Hopefully this will help anyone else encountering this error.
希望这能帮助其他人遇到这个错误。
#3
10
I had same problem and solved ... after selecting top 200 row of your table, click the "Show Criteria Pane"(left side of toolbar) now uncheck your ntext column (the column holding a big text you was talking about) now you can delete or update any row :)
我遇到了同样的问题并解决了……在选择表的前200行后，单击“显示标准窗格”(工具栏的左侧)，现在取消检查ntext列(包含您正在谈论的大文本的列)，现在可以删除或更新任何行:
#4
1
I changed my data type to nvarchar(MAX) and was able to edit and delete as I saw fit.
我将我的数据类型更改为nvarchar(MAX)，并能够根据需要进行编辑和删除。
#5
0
I discovered there was a trigger that audited changes to the record (thanks Tomas Vana), but the target audit record had a limited varchar field for the action report (varchar(1000)). It was trying to audit the deletion of records that had varchar(max) fields...duh!
我发现有一个触发器审计了对记录的更改(感谢Tomas Vana)，但是目标审计记录的操作报告的varchar字段有限(varchar(1000))。它试图审计删除包含varchar(max)字段的记录…
I changed the action report field in the log to varchar(max) and that fixed it... bloody obvious really...:)
我将日志中的action report字段更改为varchar(max)，并对此进行了修复……血腥明显真的…:)
#1
4
Normally this kind of error happens when you're inserting a long value into the column that can't store it. Check whether you don't have any triggers or any related logic that could do that on your behalf when you're deleting the row (e.g. for logging purposes / audit trail)
通常，当您将一个长值插入无法存储它的列时，会发生这种错误。检查在删除行时是否没有任何触发器或任何相关逻辑可以代表您执行此操作(例如，用于日志记录或审计跟踪)
#2
24
Though I'm late to the party, when I review the original poster's description of the problem, it sounds like they are trying to delete the row from within the table editor interface. I just encountered this same issue with a table containing a "text" column with long text, and it appears that the issue may in fact be tied to a limitation in the editor itself. From my own investigation, it appears that you will not be able to edit a row in which a text column exceeds 4000 characters. I tested with SSMS 2008 R2.
虽然我已经迟到了，但当我查看原始的海报对问题的描述时，听起来好像他们试图从表编辑器界面中删除这一行。我刚刚遇到了一个包含“文本”列和长文本的表的问题，这个问题实际上可能与编辑器本身的限制有关。根据我自己的调查，似乎您无法编辑一个文本列超过4000个字符的行。我用SSMS 2008 R2进行了测试。
Hopefully this will help anyone else encountering this error.
希望这能帮助其他人遇到这个错误。
#3
10
I had same problem and solved ... after selecting top 200 row of your table, click the "Show Criteria Pane"(left side of toolbar) now uncheck your ntext column (the column holding a big text you was talking about) now you can delete or update any row :)
我遇到了同样的问题并解决了……在选择表的前200行后，单击“显示标准窗格”(工具栏的左侧)，现在取消检查ntext列(包含您正在谈论的大文本的列)，现在可以删除或更新任何行:
#4
1
I changed my data type to nvarchar(MAX) and was able to edit and delete as I saw fit.
我将我的数据类型更改为nvarchar(MAX)，并能够根据需要进行编辑和删除。
#5
0
I discovered there was a trigger that audited changes to the record (thanks Tomas Vana), but the target audit record had a limited varchar field for the action report (varchar(1000)). It was trying to audit the deletion of records that had varchar(max) fields...duh!
我发现有一个触发器审计了对记录的更改(感谢Tomas Vana)，但是目标审计记录的操作报告的varchar字段有限(varchar(1000))。它试图审计删除包含varchar(max)字段的记录…
I changed the action report field in the log to varchar(max) and that fixed it... bloody obvious really...:)
我将日志中的action report字段更改为varchar(max)，并对此进行了修复……血腥明显真的…:)