Error Message: String or binary data would be truncated. The statement has been terminated

Question

Hello,

 

I have the following problem.

When I edit a field in the SQL Server Management Studio Express then I get the following error:

Error Source: .Net SqlClient Data Provider
Error Message: String or binary data would be truncated.

The statement has been terminated

 

When I use the SQL update commando, it works fine.

Fore debugging we need to change the enabled field several times.

Is there a solution fore this?

 

 

The Table Layout

USE [Project]

GO

/****** Object: Table [dbo].[Proces] Script Date: 06/02/2008 10:01:20 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Proces](

[prio] [bigint] NOT NULL,

[depends] [int] NOT NULL,

[type] [int] NOT NULL,

[enabled] [smallint] NOT NULL,

[naam] [nvarchar](81) NOT NULL,

[param1] [text] NOT NULL,

CONSTRAINT [PK_Proces] PRIMARY KEY CLUSTERED

(

[prio] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

Answer 1

Are you copy pasting the value to the column? the error says you are updating the column with larger size than what it can accomodate. Check for blank space in the column value

 

Madhu

 

Answer 2

Ehh no, the text column (param1) does not change. The column holds a big text file, witch is working ok!

 

By chancing the enabled column value from 0 to 1 (or vice versa) will an other program do something with the text. The functionality is ok, and works fine.

 

But by chancing the ‘enabled’ column from 0 to 1 I get the error.

 

 

Answer 3

Hi,

 

Can you please tell me how to get ahold of the software for "SQL update commando".  I have 30 entries I can not delete from a SQL 2005 table.  Your help would be much appreciated!

 

Hope everyone is having a great day!

Answer 4

I am also getting the same error while trying to update a row by SQL server management studio 2005.

I have one table with ntext column with other columns.
For some records in this table, I am geting "String or binary data would be truncated" error message when trying to modify any column/cell value in SQL server management studio 2005(Right click on table and then open table, opens table data in SQL management studio, i am trying to update/delete there).

I am getting this error even when I am trying to change a single character in any column in that row,  even when i try to input minimal data in particular cell then currently it has.

The same error I am getting while trying to delete that row in SQL server management studio 2005.

I marked this happens only when the ntext column in that row contains large data in it.

I can delete/modify values in the rows in same table for which ntext column has smaller data.

It was strange because there was existing data in these records under the
'ntext' column already. In trying to update it to a single character I
noticed I received the error messages for records that had lengthy data in
it (like paragraphs long) versus the records that I was able to update that
only had a few characters which I didn't receive the error message and was
able to update it to a single character.

Since we're still in the
development stage I ran an UPDDATE statment to set all of this 'ntext' column
to null and it seems like that this did the trick.

EDIT/DELETE is only possible by firing the query in 'query window'!!!!!!!!

CAN ANYBODY TELL ME IS IT THE BUG IN SQL SERVER MANAGEMENT STUDIO 2005? IS THERE ANY FIX FOR IT?

Answer 5

Has anyone else got any idea on how to solve this? One of my colleagues is about to commit suicide...

LS

Answer 6

Hi,

ntext can contain any length of data,

You will get error "String or binary data would be truncated. The statement has been terminated." when existing column contains data having more length than new column length.

If u want your data to be preserved and to change ntext column to nvarchar then use,

ALTER TABLE dbo . Table_Name 
ALTER COLUMN Col_Name nvarchar ( MAX ) null

Ntext is deprecated in SQL 2005, If your table still contains data more than 8000 which is MAX, then that column data will be treated as Ntext

--
<Suyash/>

Answer 7

Hello,

Please, could you give us more information ?
For that, open your SQL Server Management Studio ( Express or not : the way is the same )
in the main menu, click on Tools ==> Options
click on Request execution ( the 3rd item ) , i am not sure of the exact text as i have a french SSMS
look at the second combobox on the right part  labelled SET TEXTSIZE
if the value is around 65,000 bytes, change it to 2147483647 ( max size for NText/Text/Image/NVarchar(MAX)

click on OK and test

For more explanations, could you have a look on this link ? ( in english )
http://msdn.microsoft.com/en-us/library/ms180280.aspx
You may display this page if you click on the ? near the red cross to close the Options Window in your SSMSEE/SSMSE/SSMS ( according to your version/Edition of your SQL Server )

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 8

Try to set the recovery model to Full mode. Neikel.

Answer 9

even i had the same error when altering the column from null to not null just specify the length of the datatype which will avoid this error

ALTER TABLE TALBLENAME ALTER COLUMN COLUMNNAME DATATYPE(max) NOT NULL

 

Answer 10

Hi,

Execute following query, if any record display then first you need to put value in COLUMNNAME then you can execute Alter Column.... Not Null

 

Select * From TALBLENAME where COLUMNNAME is Null

Must be show 0 record, You cannot set Not Null until there is any Null value in COLUMNNAME

 

Kamran Balater

---

Kamran

Answer 11

@Suyash Sangar Got the same error.. Your solution worked flawlessly for me! Thank you!

Answer 12

Thanks Buudy

it's was helpfull