SQL SERVER – Msg 8152, Level 16, State 14 – String or Binary Data Would be Truncated

Earlier this week, I have blogged about how to suppress Warning: Null value is eliminated by an aggregate or other SET operation SQL SERVER – Warning: Null value is Eliminated by an Aggregate or Other SET Operation. Let us learn about the error STring or Binary Data would be truncated.

If you read that blog, I mentioned during closure that this setting might cause unexpected behavior if not used properly.  First, let’s understand the error which I am talking about:

Msg 8152, Level 16, State 14, Line 8
String or binary data would be truncated.
The statement has been terminated.

I am sure that many developer might have seen this error at least once in their lifetime. This particular error message is raised by SQL Server when we try to insert long literal sting is longer than the defined table field datatype.  For example, if we try to insert a varchar with more than 100 characters into a varchar(50) field, we will get the following error. Here is an example script to reproduce the error:

USE tempdb
GO
IF OBJECT_ID ('MyTable') IS NOT NULL
DROP TABLE MyTable
GO
CREATE TABLE MyTable(Num INT, Hi VARCHAR(2), I VARCHAR(6), Am VARCHAR(2), Pinal VARCHAR(3), Who VARCHAR(9), Loves VARCHAR(11), SQLAuthority VARCHAR(10))
GO
INSERT INTO MyTable VALUES (1, 'Hi', 'I', 'Am', 'Pinal', 'Who', 'Loves', 'SQLAuthority') 
GO

I have put the same column name as the value which I am inserting so that you can easily see the cause of the error. Since I am inserting ‘Pinal’ in varchar(3) and ‘SQLAuthority’ in varchar (10), we are getting the error.

One of the problems with this message is that SQL Server doesn’t tell you which table or column has the problem. The correct way to fix the issue is to find the column causing error and correct the data or column length.

Coming back to set option, if we use ansi_warnings as OFF, the error would be suppressed and whatever can fit in the column, would be inserted, the rest would be truncated.

SET ansi_warnings OFF
GO
USE tempdb
GO
IF OBJECT_ID ('MyTable') IS NOT NULL
DROP TABLE MyTable
GO
CREATE TABLE MyTable(Num INT, Hi VARCHAR(2), I VARCHAR(6), Am VARCHAR(2), Pinal VARCHAR(3), Who VARCHAR(9), Loves VARCHAR(11), SQLAuthority VARCHAR(10))
GO
INSERT INTO MyTable VALUES (1, 'Hi', 'I', 'Am', 'Pinal', 'Who', 'Loves', 'SQLAuthority') 
GO
SELECT * FROM MyTable
GO

SQL SERVER - Msg 8152, Level 16, State 14 - String or Binary Data Would be Truncated string-truncate-01

As we can see that the error was suppressed in the above code, but we lost part of the actual data which we were supposed to have on the table. So be cautious when working with such SET options.

Conclusion – Use ANSI_WARNING OFF with care, it might have a very negative effect on your data insertion as discussed in this blog post.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – Warning: Null value is Eliminated by an Aggregate or Other SET Operation
Next Post
Interview Question of the Week #007 – How to Reindex Every Table of the Database?

Related Posts

35 Comments. Leave new

  • Thank you!
    Very helpful :)

    Reply
  • Thank you!
    You just saved me.

    Reply
  • Hi Pinal,

    I get the error below on my data upload, its a hit and miss:

    Error MSG:String or binary data would be truncated.
    The statement has been terminated.On line no: 1328 Field name:NA

    The weird part is, my data only goes to line 1327. Any help is greatly appreciated

    Reply
  • Hi Pinal,

    Is there a way to use the ansi_warnings OFF solution with linked servers? I’m trying to figure out which column is causing truncation, but when I turn ansi_warnings off before my query, I receive this error due to it being between linked servers:

    “Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.”

    Thanks for any assistance!

    Reply
  • Nice post thanks!

    Reply
  • nice article well explained

    Reply
  • Thanks for your neat & clean explanation. But Is there any chance to get this error with datatype of DATETIME?? Because I got the similar kind of issue when trying to insert the Dates with different formats. Any help is greatly thankful.

    Reply
  • Imtiaz Shalmani
    April 19, 2016 6:39 pm

    Nice post,,,, i have the error Msg 8152, Level 16, State 14, Line 8
    String or binary data would be truncated.
    The statement has been terminated.
    When i added SET ansi_warnings OFF code it executed and return no error.
    Thank you sir.

    Reply
  • I’m getting message 8152 when calling a function that assigns a char(4) value to a char(4) variable. Any idea why it would do this? (This is SQL Server, using SSMS).

    ——————————————————-
    create function myfunc(myArg1 char(4))
    returns @retTbl table
    (
    “col1” varchar(10),
    “col2” varchar(10),
    “col3” varchar(10)
    )
    as
    begin
    declare @myVar1 char(4)

    set myVar1 = myArg1 /* <<– Error occurs here. */

    insert into @retTbl
    select
    tcol1 as "col1",
    tcol2 as "col2",
    tcol3 as "col3"
    where
    tcol4 = @myVar1
    return
    end
    go
    ——————————————————-

    select * from myfunc('1234') /* <<– Function call that causes the message. */

    Msg 8152, Level 16, State 14, Line xxx
    String or binary data would be truncated.
    The statement has been terminated.

    ——————————————————-

    I tried increasing the length of @myVar1 to char(5), then char(10), then char(40), but that didn't help any. Why does it think the data is being truncated?

    Note: SET ansi_warnings OFF did make the problem go away, but I'd like to understand why the message is appearing at all.

    Thanks, Pinal!

    Reply
    • Robert Carnegie
      July 20, 2017 3:45 pm

      Late response: mine works. I note yours also has no @ in “create function myfunc(myArg1 char(4))”.

      My memory says the function should / must be called as [dbo].[functionname](), but not why. That may be from an obsolete SQL version where perhaps you were liable to create and/or invoke a function named [username].[functionname] instead? (when a username in database was equivalent to a schema)

      Maybe you misidentified the error line and the table data (which is missing in your script) doesn’t fit. If I change ” ‘says’ AS tcol3″ in my script to ‘says far too much’, I get the error.

      drop function rjac20170720a
      go

      create function rjac20170720a(@myArg1 char(4))
      returns @retTbl table
      (
      “col1” varchar(10),
      “col2” varchar(10),
      “col3” varchar(10)
      )
      as
      begin
      declare @myVar1 char(4)

      set @myVar1 = @myArg1 /* <<– Error occurs here. */

      insert into @retTbl
      select
      tcol1 as "col1",
      tcol2 as "col2",
      tcol3 as "col3"
      from
      (select 'Robert' AS tcol1, 'Carnegie' AS tcol2, 'says' AS tcol3, '1234' AS tcol4)
      says
      where
      tcol4 = @myVar1
      return
      end
      go
      —-
      select * from dbo.rjac20170720a('12345')

      Reply
  • Sorry. I didn’t list the code correctly.
    The assignment where the problem is occurring has @-signs in front of the variables …

    set @myVar1 = @myArg1 /* <<– Error occurs here. */

    Reply
  • Sopan Vaidya
    July 28, 2016 5:19 pm

    Hi Pinal,

    I am front end developer desined application form where user inserting data.

    But sometime user getting same problem while submiting his details. I am not able to track which column throwing an error. Data inserting in the multiple tables with the help of SP.

    Could you help me to find out the column where the problem occuring ?.

    Regards,
    Sopan Vaidya

    Reply
  • valmor silva
    July 30, 2016 5:13 am

    hey someone can help me ???

    Invalid value for cell row 1 collumn 4

    The changed value in this cell was not recognized as valid.
    >Net Frameworl Data Type: Byte[]
    Error Message:You cannot use the Result pane to set this Field data to values other than NULL.

    Type a value appropriate for the data type or press ESC to cancel the change.

    Reply
  • Hailegziabher Dechassa
    February 6, 2017 10:41 pm

    Thanks Pinal, It is a helpful blog

    Reply
  • Alexander Shapkin
    March 24, 2017 3:42 pm

    Thank you very much. Your posts are very helpful and your description is very nice for fast understanding and resolving problems.
    Best regards)

    Reply
  • Joseph Regallis
    May 15, 2017 8:51 pm

    Thank you.

    Reply
  • Sohail Shaukat
    July 6, 2017 2:39 pm

    That worked thanks a lot

    Reply
  • I am new in SQL I am trying to insert 13 records but it only includes 8 here is my code

    CREATE TABLE Teachers(
    TeacherId int,
    TeacherName varchar(250),
    TeacherSubject varchar(250),
    TeacherSalary int
    );
    GO
    INSERT INTO Teachers
    (TeacherId,TeacherName,TeacherSubject
    ,TeacherSalary)
    VALUES
    (1,’Abid’,’DSP’,50000),
    (2,’Arshad’,’Telecommunication’,50000),
    (3,’Fareeda’,’Basic Electronics’,45000),
    (4,’Mamnoon’,’Industrial Electronics’,40000),
    (5,’Naseer’,’Circuit Theory’,40000),
    (6,’Umair’,’Data Communication’,35000),
    (7,’Hamood’,’Micro Controller’,20000),
    (8,’Zeeshan’,’Digital Electronics’,45000),
    (9,’Ambreen’,’Data Structure’,40000),
    (10,’Zia’,’Solid State’,40000),
    (11,’Ahmed’,’Numerical Methods’,30000),
    (12,’Nadra’,’Physics’,15000),
    (13,’Nida’,’Lab assistant’,15000);

    GO

    Reply
  • CREATE TABLE Teachers(
    TeacherId int,
    TeacherName varchar(250),
    TeacherSubject varchar(250),
    TeacherSalary money
    );

    Reply
  • Tau SleepingEvil
    October 23, 2017 2:20 pm

    Thank you!

    Reply
  • how i can correct this error

    Reply

Leave a Reply

Menu