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

Once I have been asked by my junior ASP.NET programmer, why this error comes? There was a pretty much clear reason for that when you are trying to insert the value bigger than the field limit. First let us see how we can replicate the error.

--create table for demo

CREATE TABLE StringTest

(

Name varchar(5)

)

GO

--since we have varchar limit of 5 character in Name field

--we should enter the value less than equal to 5

INSERT INTO StringTest VALUES('Rites')

SELECT * FROM StringTest

GO

--if we try to insert value greater than 5 character

--we will face an error like below.

--Msg 8152, Level 16, State 14, Line 1

--String or binary data would be truncated.

--The statement has been terminated.

INSERT INTO StringTest VALUES('Ritesh')

SELECT * FROM StringTest

GO

--I STRONGLY SUGGEST you handle this kind of validation from front end itself

--even if you are tempted to do so in SQL Server, you can have two ways of doing it

--1.)

INSERT INTO StringTest VALUES(Left('Ritesh',5))

SELECT * FROM StringTest

GO

--2.)

SET ANSI_WARNINGS OFF

INSERT INTO StringTest VALUES('Ritesh')

SELECT * FROM StringTest

GO

--SET ANSI_WARNINGS OFF statement will automatically truncate the value

--and took 'Rites' from the given string and eliminate last character

--however, it is not a proper way to do so.

Hope you have enjoyed this tip.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com