How to disable errors on string truncation in SQL Server

sql-server-2000

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.

Best Solution

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