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
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)
35 Comments. Leave new
Thank you!
Very helpful :)
Thank you!
You just saved me.
Glad this was of some help.
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
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!
Nice post thanks!
nice article well explained
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.
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.
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!
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')
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. */
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
I use SQL profiler to track the statement causing error.
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.
Thanks Pinal, It is a helpful blog
My pleasure @Hailegziabher
Thank you very much. Your posts are very helpful and your description is very nice for fast understanding and resolving problems.
Best regards)
Nice to hear that Alexander.
Thank you.
That worked thanks a lot
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
CREATE TABLE Teachers(
TeacherId int,
TeacherName varchar(250),
TeacherSubject varchar(250),
TeacherSalary money
);
Thank you!
how i can correct this error