Silent Truncation of SQL Server Data Inserts

Error:

Msg 8152, Level 16, State 4, Line 1

String or binary data would be truncated.

The statement has been terminated.

This error Message will come while inserting a Value Exceed the limit of Column can Hold the value.

CREATE TABLE EXAMPLE

(

       EMP_NAME      NVARCHAR(10)

)

In the above table Example There is a column EMP_NAME we can insert only 10 Char. While we insert if exceed the above error message will come.

EXAMPLE 1:

INSERT INTO EXAMPLE(EMP_NAME) VALUES (‘ARUN VASU’) /*No Problem in this insert */

RESULE:

1 ROW AFFECTED

EXAMPLE 2:

INSERT INTO EXAMPLE(EMP_NAME) VALUES (‘ARUN SNEHA V’)

/* INSERTED TEXT CHAR COUNT IS 12. SO THE ERROR WILL COME. */

Msg 8152, Level 16, State 4, Line 1

String or binary data would be truncated.

The statement has been terminated.

SOLUTION:

BY DEFAULT ANSI_WARNINGS IS ON SO WE RECEIVED THAT ERROR MESSAGE.

IF WE MAKE ITS OFF THEN CAN ABLE TO INSERT VALUE WITHOUT ERROR MESSAGE. ITS INSERTED VALUE WITH TRUNCATE THE CONTENT OF COLUMN.

SET ANSI_WARNINGS [ON/OFF]

EXAMPLE

INSERT INTO EXAMPLE(EMP_NAME) VALUES (‘ARUN SNEHA V’)

/* WITHOUT SHOWING ERROR MESSAGE IT WILL INSERTED DATE BY TRUNCATING */

RESULT:

SELECT * FROM EXAMPLE

EMP_NAME

-------------

ARUN VASU

ARUN SNEHA   // Truncated Data