Share via

How detecting the field that causes "string or binary data would be truncated" error

pmscorca 1,007 Reputation points
2024-10-31T17:10:14.1766667+00:00

Hi,

I'm working on a SQL Server 2017 (RTM-CU31-GDR) instance and I'm testing an INSERT INTO ... SELECT ... FROM ... statement that unfortunately causes a "string or binary data would be truncated" error.

If possible, I'd like to save development time trying to detect which field/value causes this error in a simple and automatic manner without checking each field.

So, I've tried to use the clause OPTION (QUERYTRACEON 460) but it doesn't return any helpful additional message to detect the wrong field/value.

Any suggests to me, please? Thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,943 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,650 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Yitzhak Khabinsky 25,956 Reputation points
    2024-10-31T17:37:45.1566667+00:00

    Hi @pmscorca,

    It is an old SQL Server behavior that was fixed in latest version(s) without any trickery.

    Check it out few solutions here: https://www.brentozar.com/archive/2019/03/how-to-fix-the-error-string-or-binary-data-would-be-truncated/

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.