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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

nate.hughes@usa.net - 31 Aug 2005 14:22 GMT

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

Reply to this Message

Erland Sommarskog - 31 Aug 2005 23:18 GMT

> 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.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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

Reply to this Message