Monday, June 02, 2008 8:16 AM
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?
/****** Object: Table [dbo].[Proces] Script Date: 06/02/2008 10:01:20 ******/
[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
)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]
Monday, June 02, 2008 11:26 AMModerator
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
Monday, June 02, 2008 12:14 PM
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.
Saturday, June 07, 2008 4:01 PM
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!
Thursday, August 28, 2008 1:25 PMI 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?
Thursday, April 30, 2009 11:54 AMHas anyone else got any idea on how to solve this? One of my colleagues is about to commit suicide...
Monday, May 25, 2009 11:31 AMHi,
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
Monday, May 25, 2009 4:50 PMModeratorHello,
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 )
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.
Saturday, October 09, 2010 12:19 PMTry to set the recovery model to Full mode. Neikel.
Wednesday, October 05, 2011 1:12 PM
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
Tuesday, November 29, 2011 6:30 AM
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
Friday, December 09, 2011 6:47 PM@Suyash Sangar Got the same error.. Your solution worked flawlessly for me! Thank you!
Thursday, April 12, 2012 6:40 AM
it's was helpfull