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

    السؤال

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

     

    28/جمادى الأولى/1429 08:16 ص

الإجابات

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

    • تم الاقتراح كإجابة بواسطة Suyash Sangar 01/جمادى الثانية/1430 11:35 ص
    • تم التحرير بواسطة Suyash Sangar 01/جمادى الثانية/1430 12:00 م extra
    • تم وضع علامة كإجابة بواسطة Papy NormandModerator 15/محرم/1433 10:59 ص
    01/جمادى الثانية/1430 11:31 ص

جميع الردود

  • 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

     

    28/جمادى الأولى/1429 11:26 ص
    المشرف
  • 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.

     

     

    28/جمادى الأولى/1429 12:14 م
  • 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!

    03/جمادى الثانية/1429 04:01 م
  • 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?


    27/شعبان/1429 01:25 م
  • Has anyone else got any idea on how to solve this? One of my colleagues is about to commit suicide...

    LS
    05/جمادى الأولى/1430 11:54 ص
  • 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/>

    • تم الاقتراح كإجابة بواسطة Suyash Sangar 01/جمادى الثانية/1430 11:35 ص
    • تم التحرير بواسطة Suyash Sangar 01/جمادى الثانية/1430 12:00 م extra
    • تم وضع علامة كإجابة بواسطة Papy NormandModerator 15/محرم/1433 10:59 ص
    01/جمادى الثانية/1430 11:31 ص
  • 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.
    • تم التحرير بواسطة Papy NormandModerator 01/جمادى الثانية/1430 04:56 م added help link
    • تم الاقتراح كإجابة بواسطة Naomi NModerator 08/ذو القعدة/1432 04:33 ص
    01/جمادى الثانية/1430 04:50 م
    المشرف
  • Try to set the recovery model to Full mode. Neikel.
    01/ذو القعدة/1431 12:19 م
  • 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

     

    07/ذو القعدة/1432 01:12 م
  • 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
    04/محرم/1433 06:30 ص
  • @Suyash Sangar Got the same error.. Your solution worked flawlessly for me! Thank you!
    • تم التحرير بواسطة YoukkoK 14/محرم/1433 06:48 م
    • تم الاقتراح كإجابة بواسطة SurendP 07/ربيع الثاني/1433 07:51 م
    14/محرم/1433 06:47 م
  • Thanks Buudy

    it's was helpfull

    20/جمادى الأولى/1433 06:40 ص