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