none
"String or binary data would be truncated" and field specifications

    Question

  • Hi all,

     

    i have "String or binary data would be truncated" error when i try to execute an insert statment.

     

    can i find witch field is affected by this error? (for return it to the user)

     

    thank's all

    Tuesday, May 08, 2007 7:07 AM

Answers

  • I had this very same problem recently when doing SQL Reporting.  I solved by changing varchar(20) to varchar(50) in my temporary table declarations.  It so happens that some of the older records in the table were bigger than 20 so when selecting into the temporary tables an error would result.

     

    - Will

    Thursday, December 06, 2007 11:04 PM
  • As far as I am aware, there's no way to determine which column's length has been exceeded.

     

    You should add code into your stored procedure to check the lengths of variables before inserting their values into your tables, raising an error if necessary - see the example below.

     

    Again I stress that it would be better to modify the client application's code to either warn the user or to limit the number of characters they can enter into a field.

     

    Chris

     

     

    Code Snippet

    --This batch will fail with the SQL Server error message

    DECLARE @MyTable TABLE (MyID INT IDENTITY(1, 1), MyValue VARCHAR(10))

    DECLARE @MyParameter VARCHAR(100)

    --Create a string of 52 chars in length

    SET @MyParameter = REPLICATE('Z', 52)

    INSERT INTO @MyTable(MyValue)

    VALUES (@MyParameter)

    GO

     

    --This batch will fail with a custom error message

    DECLARE @MyTable TABLE (MyID INT IDENTITY(1, 1), MyValue VARCHAR(10))

    DECLARE @MyParameter VARCHAR(100)

    --Create a string of 52 chars in length

    SET @MyParameter = REPLICATE('Z', 52)

    IF LEN(@MyParameter) > 10

    BEGIN

         RAISERROR('You attempted to insert too many characters into MyTable.MyValue.', 16, 1)

    RETURN

    END

    ELSE

    BEGIN

         INSERT INTO @MyTable(MyValue)

         VALUES (@MyParameter)

    END

    GO

     

    Tuesday, May 08, 2007 7:53 AM
  • Msg 8152, Level 16, State 14, Line 7 String or binary data would be truncated.

    Answers to several of the posts above in this thread.

    You can prevent this error very easily by getting the column size metadata from INFORMATION_SCHEMA views (see T-SQL code below).

    SQL Server 2008 error message is not any different than previous versions.

    Surely it would be easy to return the offending column on a single line insert!  What if you are inserting 1 million rows with one statement and there are 100,000 offending rows?

    Well that is tough....however, incredible it may sound but SSIS can handle it. This is a normal feature of SSIS Data Flow. It filters out the offending rows, and there are several ways to do it.

    What can you do in T-SQL?

    For one-liner insert you just compare the target length with the intended insert length.

    For datasets with multiple rows, you have to do filtering like:

    1. WHERE ISNUMERIC (ColumnA) = 1
    2. WHERE ISDATE (ColumnX) = 1
    3. WHERE LEN (StagingName) <=
    (SELECT CHARACTER_MAXIMUM_LENGTH
    FROM AdventureWorks2008.INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA='Production'
      AND TABLE_NAME='Product'
      AND COLUMN_NAME = 'Name' )

    Column metadata demo.
     
    -- OBTAIN COLUMN SIZE METADATA FROM INFORMATION_SCHEMA VIEW
    SELECT ColumnName=COLUMN_NAME, Limit=CHARACTER_MAXIMUM_LENGTH
    INTO #MaxLengths
    FROM AdventureWorks2008.INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA='Production'
      AND TABLE_NAME='Product'
      AND DATA_TYPE IN ('varchar','nvarchar','char','nchar')
    SELECT * FROM #MaxLengths
    GO
    /*
    ColumnName				Limit
    Name					50
    ProductNumber			        25
    Color					15
    Size					5
    SizeUnitMeasureCode		       3
    WeightUnitMeasureCode	               3
    ProductLine				2
    Class					2
    Style					2
    */
    DROP TABLE #MaxLengths

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
    Saturday, November 21, 2009 2:20 PM
    Moderator

All replies

  • If possible it would be far better to truncate the string to the maximum allowable length within the client application (and warn the user if necessary) before passing it to SQL Server for insertion into the database.

     

    For debug purposes you could run SQL Profiler to witness the values of the parameters being passed into the stored procedure then work through the SQL code and locate where the error is being caused.

     

    Chris

    Tuesday, May 08, 2007 7:31 AM
  • this is not possible because client and db musn't be linked (db can be modified). i don't know futur size of these fields.

     

    it's a feature for my users, indicating whitch field is too long

    Tuesday, May 08, 2007 7:40 AM
  • As far as I am aware, there's no way to determine which column's length has been exceeded.

     

    You should add code into your stored procedure to check the lengths of variables before inserting their values into your tables, raising an error if necessary - see the example below.

     

    Again I stress that it would be better to modify the client application's code to either warn the user or to limit the number of characters they can enter into a field.

     

    Chris

     

     

    Code Snippet

    --This batch will fail with the SQL Server error message

    DECLARE @MyTable TABLE (MyID INT IDENTITY(1, 1), MyValue VARCHAR(10))

    DECLARE @MyParameter VARCHAR(100)

    --Create a string of 52 chars in length

    SET @MyParameter = REPLICATE('Z', 52)

    INSERT INTO @MyTable(MyValue)

    VALUES (@MyParameter)

    GO

     

    --This batch will fail with a custom error message

    DECLARE @MyTable TABLE (MyID INT IDENTITY(1, 1), MyValue VARCHAR(10))

    DECLARE @MyParameter VARCHAR(100)

    --Create a string of 52 chars in length

    SET @MyParameter = REPLICATE('Z', 52)

    IF LEN(@MyParameter) > 10

    BEGIN

         RAISERROR('You attempted to insert too many characters into MyTable.MyValue.', 16, 1)

    RETURN

    END

    ELSE

    BEGIN

         INSERT INTO @MyTable(MyValue)

         VALUES (@MyParameter)

    END

    GO

     

    Tuesday, May 08, 2007 7:53 AM
  • I had this very same problem recently when doing SQL Reporting.  I solved by changing varchar(20) to varchar(50) in my temporary table declarations.  It so happens that some of the older records in the table were bigger than 20 so when selecting into the temporary tables an error would result.

     

    - Will

    Thursday, December 06, 2007 11:04 PM
  • [Will wrote:

     

    I had this very same problem recently when doing SQL Reporting.  I solved by changing varchar(20) to varchar(50) in my temporary table declarations.  It so happens that some of the older records in the table were bigger than 20 so when selecting into the temporary tables an error would result.

     

    - Will]

     

    Your solution took care of my problem. It was small and often small things are hard to detect.. Wink thanx.

    Tuesday, June 10, 2008 2:19 PM
  • Welcome to the Transact SQL Forum, Tins.  I hope you will continue to contribute. 

     

     

    Kent

     

    EDIT:

     

    Also, would you be kind enough to click "Yes" for the "Helpful" button for Will?

    Tuesday, June 10, 2008 2:24 PM
    Moderator
  • Yes, your simple reply cleared my problem. Smile thk u.
    Saturday, June 21, 2008 2:13 AM
  • I just found a GREAT way around this common problem:

    Select *
    into TempTable
    from SourceTable

    Then do a schema comparison in SQL Server (Generate a Create Script) on TempTable and compare it to DestinationTable any differences become visually appearant..

    -David S.

    Axos Technologies, Inc.

    • Proposed as answer by reeky Sunday, December 27, 2009 6:45 PM
    Tuesday, July 08, 2008 1:20 AM
  • I was facing the same problem, didn’t find any elegant way of finding this error. I grouped all my insert columns and kept on commenting some block, after narrowing down the selection criteria finally I was able to find the culprit.

     

    Friday, August 01, 2008 2:14 AM
  •  

    Is SQL2008 more verbose about these errors? or any patch or special configuration settings for SQL2005?
    Friday, August 01, 2008 8:32 AM
  • To my knowledge major re-work on the error messages is not a trumpeted feature of 2008.  This error will always mean that some column in your statement was not wide enough to hold data you were inserting or updating into it, and your varchar (or similar) columns are going to be the most likely culprits, Varbinary columns are likely too but are less used than varchar.
    Monday, August 04, 2008 5:45 PM
  •  

    Thanks Will, I had same problem before and solved now.

     

     

    Thursday, August 07, 2008 4:24 PM
  •  

    Hi all,

     

    i have a "String or binary data would be truncated" error when i try to insert a date.

     

    i have a column "date" and i choose smalldatetime for it. In my application i have  used a textbox a button and a calendar.

    the code i wrote to show the calendar is:

     

    Protected Sub Calendar1_SelectionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Calendar1.SelectionChanged

    TextBox4.Text = Calendar1.SelectedDate.ToShortDateString

    Calendar1.Visible = False

    End Sub

    Protected Sub cal1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cal1.Click

    Try

    If (TextBox4.Text.Trim IsNot "") Then

    Calendar1.SelectedDate = Convert.ToDateTime(TextBox4.Text)

    End If

    Catch ex As Exception

    System.Console.Write(ex.Message.ToString)

    End Try

    Calendar1.Visible = True

    End Sub

     

     

     

    it works fine but when i run my program and choose a date to insert in my table i get the error i mention above.

     

    i don't know what to do!!

     

    thanks

    Wednesday, November 19, 2008 3:33 PM
  • Try using the below in your stored proc.

    Set NoCount On;
    Monday, January 19, 2009 9:09 AM
  • Hello

    This is notorious error in SQL. What's happening, is that we are inserting or updating nchar or varchar columns with values that are larger than the defined size of those columns:

    Example : we have table called MomoTable and Column Called MomoColumn with size of 10 nvarchar

    we are inserting value of nvarchar 200 then we should increase the size of our column as follows:

    Solution in for the exmaple :

     

    ALTER TABLE MomoTable  ALTER COLUMN  MomoColumn nvarchar(255)

    GO

     

    Solution in general Theoric format :

     

    ALTER TABLE [TableName]  ALTER COLUMN  [ColumnName] nvarchar(255)

    GO

     

    Cheers


    Momo
    Friday, November 13, 2009 3:43 PM
  • Msg 8152, Level 16, State 14, Line 7 String or binary data would be truncated.

    Answers to several of the posts above in this thread.

    You can prevent this error very easily by getting the column size metadata from INFORMATION_SCHEMA views (see T-SQL code below).

    SQL Server 2008 error message is not any different than previous versions.

    Surely it would be easy to return the offending column on a single line insert!  What if you are inserting 1 million rows with one statement and there are 100,000 offending rows?

    Well that is tough....however, incredible it may sound but SSIS can handle it. This is a normal feature of SSIS Data Flow. It filters out the offending rows, and there are several ways to do it.

    What can you do in T-SQL?

    For one-liner insert you just compare the target length with the intended insert length.

    For datasets with multiple rows, you have to do filtering like:

    1. WHERE ISNUMERIC (ColumnA) = 1
    2. WHERE ISDATE (ColumnX) = 1
    3. WHERE LEN (StagingName) <=
    (SELECT CHARACTER_MAXIMUM_LENGTH
    FROM AdventureWorks2008.INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA='Production'
      AND TABLE_NAME='Product'
      AND COLUMN_NAME = 'Name' )

    Column metadata demo.
     
    -- OBTAIN COLUMN SIZE METADATA FROM INFORMATION_SCHEMA VIEW
    SELECT ColumnName=COLUMN_NAME, Limit=CHARACTER_MAXIMUM_LENGTH
    INTO #MaxLengths
    FROM AdventureWorks2008.INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA='Production'
      AND TABLE_NAME='Product'
      AND DATA_TYPE IN ('varchar','nvarchar','char','nchar')
    SELECT * FROM #MaxLengths
    GO
    /*
    ColumnName				Limit
    Name					50
    ProductNumber			        25
    Color					15
    Size					5
    SizeUnitMeasureCode		       3
    WeightUnitMeasureCode	               3
    ProductLine				2
    Class					2
    Style					2
    */
    DROP TABLE #MaxLengths

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
    Saturday, November 21, 2009 2:20 PM
    Moderator
  • I hope you folks know the msg 8152 is simply a warning. If you don't care your data been chopped off, simply run SET ANSI_WARNINGS OFF, you get rid of this error, your program or process will run just fine. This answer may not fit all people but it's one of the option you need to know about it.
    Friday, January 15, 2010 7:17 PM
  • Well I believe I have proven a Bug in SQL 2008.  I'm getting this error but none of my columns have oversize data.

    I have proven it as follows:

    INSERT INTO Table1
    Select
    <Big list of columns>
    From
    <big list of tables>

    The above fails.  So:

    Select
    <Big list of columns>
    INTO Table2
    From
    < big list of tables>

    This works as columns are created based on the size of the source data.

    Now to try and find the data that is too big you run this script, replacing the table names with yours, and paste the results into a new query:

    --SELECT CLAUSE followed by MyKey = <Unique column identifier>
    select ',['+c.name+'] = case when max(len(['+c.name+'])) > '+ltrim(str(c.max_length))+' then 1 else 0 End'
    from sys.columns  c
    join sys.objects o
    on o.object_id=c.object_id
    and o.name = '<table1>'
    and system_type_id in (35,99,167,231,175,239) -- this looks at Text,nText, Varchar,nVarchar,char and nchar)
    --GROUP BY CLAUSE
    select ',['+c.name+']'
    from sys.columns  c
    join sys.objects o
    on o.object_id=c.object_id
    and o.name = '<table1>'
    and system_type_id in (35,99,167,231,175,239) -- this looks at Text,nText, Varchar,nVarchar,nchar)

    --WHERE CLAUSE
    select ',['+c.name+'] =1 OR'
    from sys.columns  c
    join sys.objects o
    on o.object_id=c.object_id
    and o.name = '<table1>'
    and system_type_id in (35,99,167,231,175,239) -- this looks at Text,nText, Varchar,nVarchar,nchar)


    You need to type in the the SELECT Command and some Key column to let you retrieve the problem data later and delete the first  comma

    Follow the list with an into #mytemp

    Type in the FROM <table2> Clause

    Type in the GROUP BY Clause and remove the first comma.

    Run the resulting SQL

    Start a new Query:
    Select * from #mytemp
    Type in the WHERE clause and remove the last OR

    In my case it shows that none of my string data is over size and I don't have any binary data.

    My insert script used to work fine in SQL 2005.

    You could perhpas avoid the temporary table by using a HAVING Clause but when problem tracking I like to have small steps.

    My work around was to take the scheme for Table 2, reduce some of the needless Varchar(255) to somthing more sensible and use it to replace the table1 schema.

    Thursday, July 15, 2010 11:55 AM
  • I am using SQL Server 2008 Enterprise edition SP2 trial version.

    I have a table that have 5 columns all varchar having a total length upto around 40 characters.

    Now I am trying to execute an t-sql script that has 3-4 insert statements and its failing with 'String or Binary data would be truncated' and execution is failed.

    If I execute these statements one by one they all execute successfully with no error.

    I found that if the length of t-sql batch is less than 256 (approximate) character its executing but if more than 256 its failing.

    I also found that same batch (even more than 256 charcahter long) is successfully executing in SQL Server 2005.

    I tried using the SET ANSI_WARNINGS OFF also but that didn't help.

    I haven't found any setting to change the maximum size limit of batch.

    Thanks in advance.

    Subhash Chandra

    Thursday, September 09, 2010 4:58 PM
  • Thanks friend...

    I got my issue resolved by checking this.....post

    Friday, May 27, 2011 9:14 PM
  • This fixed my issue.  Thank you!!!
    Friday, June 24, 2011 10:23 PM
  • It is helpful for the forum readersl to describe exactly what fixed what issue. Thanks.
    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
    Wednesday, June 29, 2011 11:24 PM
    Moderator
  • Brilliant. This is the kind of answer which I think was begged by the question. It certainly helped me - thanks.
    Thursday, September 29, 2011 9:12 PM
  • You may find this blog post on this exact problem helpful as well

    How to find what column caused the String or binary data would be truncated message


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, October 03, 2011 1:22 AM
    Moderator
  • for the  CHARACTER_MAXIMUM_LENGTH is coming as -1 does it means that it is max ie nvarchar(max)

    Wednesday, June 06, 2012 10:45 AM
  • Yes, it means it's either varchar(max) or nvarchar(max) depending on the type.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, June 06, 2012 12:59 PM
    Moderator
  • thanks Chris
    Saturday, November 10, 2012 3:52 AM
  • Hi,

    Try increasing the datatype length of the table where you want to insert.


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Saturday, November 10, 2012 10:43 AM
  • Ya that's a datatype issue. When you try to insert something more than the length of permitted data type

    Wednesday, November 28, 2012 8:00 PM