none
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]

     

    Monday, June 02, 2008 8:16 AM

Answers

  • 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/>

    Monday, May 25, 2009 11:31 AM

All replies

  • 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

     

    Monday, June 02, 2008 11:26 AM
  • 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.

     

     

    Monday, June 02, 2008 12:14 PM
  • 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!

    Saturday, June 07, 2008 4:01 PM
  • 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?


    Thursday, August 28, 2008 1:25 PM
  • Has anyone else got any idea on how to solve this? One of my colleagues is about to commit suicide...

    LS
    Thursday, April 30, 2009 11:54 AM
  • 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/>

    Monday, May 25, 2009 11:31 AM
  • 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.
    Monday, May 25, 2009 4:50 PM
  • Try to set the recovery model to Full mode. Neikel.
    Saturday, October 09, 2010 12:19 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

     

    Wednesday, October 05, 2011 1:12 PM
  • 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
    Tuesday, November 29, 2011 6:30 AM
  • @Suyash Sangar Got the same error.. Your solution worked flawlessly for me! Thank you!
    • Edited by YoukkoK Friday, December 09, 2011 6:48 PM
    • Proposed as answer by SurendP Wednesday, February 29, 2012 7:51 PM
    Friday, December 09, 2011 6:47 PM
  • Thanks Buudy

    it's was helpfull

    Thursday, April 12, 2012 6:40 AM