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

Advertisements

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

  1. Nilesh Patel Says:

    Fantastics and useful Blog, i will always check your bolg before googling another site 🙂

  2. Nilesh Patel Says:

    Fantastics and useful Blog, i will always check your bolg before googling another site 🙂

  3. Ritesh Shah Says:

    thank you very much Nilesh Patel for your comment

  4. Ritesh Shah Says:

    thank you very much Nilesh Patel for your comment


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: