Why are my insert, update statements failing with the following error? Server: Msg 8152, Level 16, State 9, Line 1 String or binary data would be truncated

This error occurs, when the length of the value entered by you into a char, varchar, nchar, nvarchar column is longer than the maximum length of the column. For example, inserting 'FAQ' into a char(2) column would result in this error.

Profiler is handy in troubleshooting this error. If data truncation is okay with you and you don't want to see this error, then turn off ANSI WARNINGS by using the following SET command: SET ANSI_WARNINGS OFF.

Steps to reproduce the problem:CREATE TABLE MyTable
(
Pkey int PRIMARY KEY,
Col1 char(10)
)
GO
INSERT INTO MyTable (Pkey, Col1) VALUES (1, 'SQL Server Clustering FAQ')
GO
Make sure, you restrict the length of input, in your front-end applications. Check length of input before inserting or updating in Database.


For example, you could use the MAXLENGTH property of the text boxes in HTML forms. 

String or binary data would be truncated

This error is coming due to:
You are doing INSERTing or UPDATEing a string column, but the length of the string you are passing in is too large.

Here is an example to recreate the problem.

create table Test  (Name VarChar(10))

INSERT INTO Test Values ('Manish') --This Works

INSERT INTO Test Values ('Manish Agarwal') --This Fails

You can use the SubString command to reduce the size of the input value to 10 characters such as this:

INSERT INTO Test Values (SubSting('Manish Agarwal', 1, 10))