Weird happenings with Ansi Warnings setting on SQL 2005

[Ovatvvon]

Hi all,

The Problem
I have a client who is testing their app with SQL 2005. It was working fine in SQL 2000. On the new DBMS, when a query is run (which was not causing problems in SQL 2000), it is giving an error stating:

Server: Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

After some troubleshooting, we figured out that if he specified Ansi Warnings to be Off, it worked, but if it specified them to be On, or did not specify anything at all, then it would fail with the above error.

SET ANSI_WARNINGS off

When I look at the SQL Server properties, I see that ansi warnings are already set to off by default. It is the same when I look at the properties of his databases. So why it wouldn't work by default, but it does when he specifically gives the command to turn them off is very confusing.

The Question
Does anyone know if there is another setting somewhere to override the two I already looked at, which may have the ANSI_WARNINGS set to On by default? Or if there is anything else that may be impacting this? Any ideas?



-Ovatvvon :-Q

 

[ESquared]

Don't forget the distinct possibility that string or binary data really is getting truncated, and that the solution may be in fixing that instead of simply suppressing the warning message...

Though what you describe sounds mysterious and I don't know how to help you.