How do I disable errors on string truncation in SQL Server?

sql-server-2000
Ewan Makepeace picture Ewan Makepeace · Feb 9, 2009 · Viewed 23.5k times · Source

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.

Answer

SQLMenace picture SQLMenace · Feb 9, 2009

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

Related questions

Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Check if table exists in SQL Server

I would like this to be the ultimate discussion on how to check if a table exists in SQL Server 2000/2005 using SQL Statements. When you Google for the answer, you get so many different answers. Is there an official/backward …

Read More
How to Join to first row

I'll use a concrete, but hypothetical, example. Each Order normally has only one line item: Orders: OrderGUID OrderNumber ========= ============ {FFB2...} STL-7442-1 {3EC6...} MPT-9931-8A LineItems: LineItemGUID Order ID Quantity Description ============ ======== ======== ================================= {098FBE3...} 1 7 prefabulated amulite {1609B09...} 2 32 spurving bearing But occasionally there …

Read More