Some rows giving "String or binary data would be truncated" error when editing in Management Studio

Question

When trying to add data to a newly created column using SQL Server Management Studio (edit rows), I am getting the following error:

No row was updated.

The data in row 1 was not committed.

Error Source: .Net SqlClient Data Provider.

Error Message: String or binary data would be truncated.

The statement has been terminated.

Correct the errors and retry or press ESC to cancel the change(s).

The datatype for the column is nvarchar(150).

---

UPDATE:
I am inserting only a few ascii characters (example: abc).

This is someone else's database who asked me to look into it... I discovered: a) only some of the rows give this error b) the rows giving this error have long strings in two other columns (one is a nvarchar(max), and one is ntext).

Solution 1

I've experienced this on occasions when a table had a mixture of ntext and nvarchar(MAX) datatypes. Changing all the ntexts to nvarchar(MAX)s fixes the problem.

Solution 2

This error typically occurs when you exceed the length of the data type for the column. Are you inserting more than 150 characters into the column?

Solution 3

I got this error when I mistakenly exceeded the number of characters that a field could hold. In Visual Studio, I had mistakenly inserted a field with its datatype as nvarchar(1) and was trying to save a word with 6 characters to that field. Once I changed to nvarchar(50), it worked fine. Hope this helps someone.

Comments

[Dave Holland]

Is there only a single column in that table? What are the types and lengths of all the columns and what does your insert/update statement look like?

[Zero-dev]

There are multiple columns, see my update above.

[Zero-dev]

I'm only inserting a few characters. I updated some new findings in the post.

[Brian Knight]

Hmmm - It’s possible to ignore the 'String or binary data would be truncated' message by setting ANSI_WARNINGS to OFF. This will truncate fields where they don’t fit. ANSI_WARNINGS OFF has drawbacks and it is better to correct a problem rather than ignore it. Another thing to check is whether the table update is firing any triggers that insert or update another table.

[Massimo]

Whoever designed a table with a nvarchar(1) column lacked some very basic knowledge about databases.