Menu
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?
Considerable : i fix it by drop table and create it again, but i wanna a Solution!
241
Solution. To avoid this error and to insert the string with truncation, use the ANSI_WARNINGS option. On setting ANSI_WARNINGS to OFF, the error message will not be displayed and the data will be automatically truncated to the length of the destination column and inserted.
What is “String or binary data would be truncated” One of the most common SQL Server errors, the message “String or binary data would be truncated” occurs when a value is trying to be inserted or updated in a table and it is larger than the maximum field size.
It simply means that you are inserting a value that is greater than the maximum allowed value. Ex, a column can only hold up to 5 characters, but you are inserting 10-character string.
A binary string is a sequence of bytes. Unlike a character string which usually contains text data, a binary string is used to hold non-traditional data such as pictures. The length of a binary string is the number of bytes in the sequence. A binary string has a CCSID of 65535.
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.
Hopefully this will help anyone else encountering this error.
66
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 :)
43
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
