Menu
How can I tell SQL Server not to raise an error if I insert or update a string longer than the size of the field - I would like silent truncation in this instance.
543
Solution. To avoid this error and to insert the string with truncation, use the ANSI_WARNINGS option. On setting ANSI_WARNINGS to OFF, the error message will not be displayed and the data will be automatically truncated to the length of the destination column and inserted.
To fix this error, patch to SQL Server 2016 SP2, CU6 or newer (including SQL Server 2017), and then turn on trace flag 460. You can enable it at the query level or at the server level.
What is “String or binary data would be truncated” One of the most common SQL Server errors, the message “String or binary data would be truncated” occurs when a value is trying to be inserted or updated in a table and it is larger than the maximum field size.
The thing you have to do is set ANSI WARNINGS to OFF You can do that by calling
set ANSI_WARNINGS OFF
I have also written a practical example:
create table bla(id varchar(2))
go
insert bla values ('123') --fails
set ANSI_WARNINGS OFF
insert bla values ('123') --succeeds
Do remember to turn the ANSI warnings back ON when you are done. You can do so by calling:
set ANSI_WARNINGS ON
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
