By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,084 Members | 1,207 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,084 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Aug 31 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
(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

Aug 31 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.