How to see in which column data truncation has happened

Question

Inserting data into a Microsoft SQL Server database using Java, I got an SQL truncation exception. But the exception doesn't show which column had the problem value.

Is there any way to find the error details, for instance from SQL Server Management Studio?

Note: I am using SQL Server database in Windows.

Solution

Unfortunately, no. The error message is as much information as SQL Server will give you.

To discover what values in what columns of which rows are at fault, you need to analyze the data. A common approach to discovering this sort of issue is to perform binary chops - remove half of the data or half of the columns, attempt the insert again (possibly into a temp table with the same structure as the real table) and see if the error recurs.

If it does, great, at least one problem is in the data that remains. If not, then you've eliminated half of the data as not causing a problem.

Comments

[Rajasekhar]

Java is printing "java.sql.DataTruncation: Data truncation and stack trace". But, it is not giving any further details about which data or column has been truncated.

[Damien_The_Unbeliever]

@Rajasekhar - As I said, there's no more help provided by SQL Server when this happens - you need to search for the data causing the error. As I also said, a common way to attempt to locate it is to chop the amount of data involved in half (either by removing columns or removing rows) and seeing whether you still get the error

[Rajasekhar]

Thanks for your suggestion.