SET ANSI_WARNINGS OFF in stored procs - how to avoid recompilation?

[nate.hughes]

Ok, here's my dilemma. We're running SQL Server 2000 with the default
db setting for the ANSI_WARNINGS option set to off. However, we still
get "Warning: Null value is eliminated by an aggregate or other SET
operation" messages. To eliminate getting the message, we use the SET
ANSI_WARNINGS OFF in our stored procs. Using that SET command forces a
recompilation of the SP and is causing locks/blocking. Anybody else
run into this problem and if so, how have you handled it.

Thanks,
-Nate

[Erland Sommarskog]

(na*********@usa.net) writes:

Ok, here's my dilemma. We're running SQL Server 2000 with the default
db setting for the ANSI_WARNINGS option set to off.
There are several places where ANSI_WARNINGS can be set, and the principle
is simple: if it's set somewhere it set.

All client API activate ANSI_WARNINGS by default, except for DB-Library
and maybe really old versions of the ODBC driver.
However, we still get "Warning: Null value is eliminated by an aggregate
or other SET operation" messages. To eliminate getting the message, we
use the SET ANSI_WARNINGS OFF in our stored procs. Using that SET
command forces a recompilation of the SP and is causing locks/blocking.
Anybody else run into this problem and if so, how have you handled it.

SUM(coalesce(col, 0))

and things like that.

But since this is an informational message, it should not cause any
major problem.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp