Isolating "String or binary data would be truncated" errors

Sometimes when Jiwa saves data to the database, an error may occur “String or binary data would be truncated” if the data being saved into a field exceeds the table column length.

Often importing or other functions will surface this error and troubleshooting which field and row of data causing the issue can be cumbersome.

By opting into the SQL Server trace flag 460, the message presented can be expanded to include the field and the data contents, enabling the identification of which piece of data is causing the error.

You can enable the trace flag by running the following SQL Query against the Server:

And to disable the traceflag:

For example, forcing a truncation error using SSMS using the following command against a Jiwa Demo Database:

Will yield the error:

Msg 8152, Level 16, State 30, Line 3
String or binary data would be truncated.
The statement has been terminated.

Completion time: 2024-03-18T08:56:46.8230835+08:00

And when the trace flag is enabled:

Msg 2628, Level 16, State 1, Line 3
String or binary data would be truncated in table 'JiwaDemo.dbo.IN_Main', column 'Aux1'. Truncated value: '123456789123456789123456789123456789123456789*'.
The statement has been terminated.

Completion time: 2024-03-18T08:58:32.9972640+08:00

Because the field is now shown in the error, as well as the data value causing the truncation, encountering errors in functions such as imports will be able to be corrected much more easily.