1

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).

Feckmore
  • 145
  • 2
  • 3
  • 7

5 Answers5

3

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?

Brian Knight
  • 1,175
  • 2
  • 8
  • 17
  • I'm only inserting a few characters. I updated some new findings in the post. – Feckmore Mar 02 '10 at 14:00
  • 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. – Brian Knight Mar 02 '10 at 14:07
3

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.

graphicdivine
  • 146
  • 2
1

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.

Dev
  • 111
  • 2
  • Whoever designed a table with a **nvarchar(1)** column lacked some very basic knowledge about databases. – Massimo Sep 10 '21 at 22:55
0

Usually this occurs when tried insert more symbols, what is given to this field. go to Design, check the Data Type of field for Example your Data Type = nvarchar(20) and your tried set the 30 characters

Microsoft SQL Server 2014

maxi8080
  • 1
0

I encountered this error when trying to edit a row in a table that had even one field of type text. It still seems to be an issue even in SSMS 18 (in 2021).

Eric Barr
  • 101
  • 2