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.