This
error is usually encountered when inserting a record in a table where one of
the columns is a VARCHAR or CHAR data type and the length of the value being
inserted is longer than the length of the column. Before SQL 2019, it is
really frustrated with this error message because you cannot find which column
is the offending column. The error message is informative now in SQL
Server 2019, which tells you which column is causing the issue.
We have a table before 2019 version of sql server
create table table_test
(Id int identity (1,1),
Name varchar(10),
Address varchar(10),
Mobile varchar(10))
|
Now insert some records
Insert into table_test
(Name,Address,Mobile) values
('Bagesh Kumar Singh','Noida','888880XXXX')
|
Getting bellow error
It is very default to find out in which column it throws
this error.
In sql server 2019, this error message has been improved. Here
we will get the column name with truncate value.
Let’s see this example in sql server 2019
Insert this record
Oooooo again we are
getting same error!
Don’t warry
We need to set the VERBOSE_TRUNCATION_WARNINGS ON.
Now I am altering this database and setting VERBOSE_TRUNCATION_WARNINGS on.
ALTER DATABASE SCOPED CONFIGURATION
SET VERBOSE_TRUNCATION_WARNINGS = on;
|
Now I am inserting this record.
With the help of this error
message, we can easily find out the truncated column and value. Now I am
correcting it and inserting now.
Record inserted successfully.
