SEAH SZE YIN (2/15/2008)

---

Please allow me to further explain my concern here:

The SQL script i posted here is extracted from an existing stored procedure of my system using SQL2000 and it is working fine without return an error. I just upgrade my server to SQL 2005 and error return.

I am thinking that we have so many stored procedure in system, how can i know which stored procedure will works fine in sql2000 but return this kind of error in SQL2005 as well?

So i am looking for a way to bypass this truncate error at database or server level instead of stored procedure.

Please advice. Thanks.

When set to ON, the divide-by-zero and arithmetic overflow errors cause the statement to be rolled back and an error message is generated. When set to OFF, the divide-by-zero and arithmetic overflow errors cause null values to be returned. The behavior in which a divide-by-zero or arithmetic overflow error causes null values to be returned occurs if an INSERT or UPDATE is tried on a character, Unicode, or binary column in which the length of a new value exceeds the maximum size of the column. If SET ANSI_WARNINGS is ON, the INSERT or UPDATE is canceled as specified by the SQL-92 standard. Trailing blanks are ignored for character columns and trailing nulls are ignored for binary columns. When OFF, data is truncated to the size of the column and the statement succeeds.