String or Binary data would be truncated¶

Cause¶

This message is shown when the data being inserted/updated is larger than the size of the column.
For example, if user is entering ‘ Amir Shrestha ’ (13 Characters long) into the Name column that supports only 10 characters ( VARCHAR(10) ).

Solution¶

The solution to this issue is to either shorten the value being updated or to update the field to allow larger values.
But the message does not tell anything about the location ( Table & Column ) where the issue is occurring.

In recent versions of SQL Server, special traces can be enabled that shows on exact table, column & value that is causing the issue.
This feature is available on SQL Server 2017(with Cumulative Update) and above only.
for older version only option is to dig through SQL Server Profiler .

Update SQL Server 2017¶

• Get the Cumulative Update file KB5016884 from the FTP Server .
• Ensure that none of client terminal is accessing the Database Server.
• Install Cumulative Update file KB5016884 .

Enable trace to pinpoint the cause of issue¶

• Run DBCC TRACEON(460, -1) ; command to enable trace globally.
• Redo the steps that caused the message to popup
• This time the message should include table, column & value that is causing the message.

• Either shorten the value or update the field to allow larger values.
• Run DBCC TRACEOFF(460,-1); to disable the trace. ( Very Important )

Flow-Chart¶