SET ANSI_WARNINGS OFF is being overwritten somewhere
Murphy@discussions.microsoft.com Laurie Murphy
using is SET ANSI_WARNINGS OFF. Several of the stored procedures and triggers
I inherited were setting them OFF at the beginning and back to ON at the end.
This was causing 2 recompiles every time any of these objects were fired or
called. Because we were encountering locks and timeouts, the DBAs asked me to
remove them since it was the default for the database anyway. Well,
unexpected results in the application (VB6) so I have tried everything to
avoid putting the settings inside the sps and triggers. I have added "ANSINPW
= off" to the connection string and still my sps are returning incorrect
recordsets.
I have been researching for 3 days and trying different things but nothing
has worked so far. Any suggestions would be greatly appreciated.
Razvan Socol
warnings even if SET ANSI_WARNINGS is ON.
(Post an example and we'll try to help)
Razvan
PS. It is useless to set any option at the end of a procedure, since
the option will be reset to the value it had when the procedure was
started, anyway.
Erland Sommarskog
> I have checked with our DBAs and the default setting for the database I
> am using is SET ANSI_WARNINGS OFF. Several of the stored procedures and
> triggers I inherited were setting them OFF at the beginning and back to
> ON at the end. This was causing 2 recompiles every time any of these
> objects were fired or called. Because we were encountering locks and
> timeouts, the DBAs asked me to remove them since it was the default for
> the database anyway. Well, unexpected results in the application (VB6)
> so I have tried everything to avoid putting the settings inside the sps
> and triggers. I have added "ANSINPW = off" to the connection string and
> still my sps are returning incorrect recordsets.
There is all reason to run with ANSI_WARNINGS ON, as there are features
in SQL Server that requires this setting to be on. It also has the good
effect that with this setting in effect errors like overflow and
division by zero does not go unnoticed.
I don't know exactly what incorrect recordsets you get. My impression
is that the noise message about NULL in aggregates being ignored is
just dropped on the floor by ADO. But it may be a good idea to have
SET NOCOUNT ON everywhere.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Laurie Murphy
require literally hundreds of stored procedures and triggers to be rewritten.
This is not possible at this time. One of the stored procedures, which causes
a trigger to fire that gets the null warning, has as its last statement:
"Select @case_sid as new_case_sid. When I debug the VB application, the
recordset value comes back with "Item cannot be found in the collection
corresponding to the requested name or ordinal." and causes my app to throw
an error.
In the future, I will recommend writing all of our queries differently to
protect against null values but for now what can I do? Where can I set ansi
warnings off so they stay off?
Razvan Socol
<LaurieMur...@discussions.microsoft.com> wrote:
> [...] One of the stored procedures, which causes
> a trigger to fire that gets the null warning, has as its last statement:
> "Select @case_sid as new_case_sid. When I debug the VB application, the
> recordset value comes back with "Item cannot be found in the collection
> corresponding to the requested name or ordinal." and causes my app to throw
The last statement is not the cause of the problem. The warning is
caused by some previous statement and this makes ADO to generate an
empty recordset for each message/resultset returned by the SP. To get
to your real recordset, you should execute:
Do While rcs.State = adStateClosed
Set rcs = rcs.NextRecordset
If rcs Is Nothing Then Exit Do
Loop
Usually, the warning is "Warning: Null value is eliminated by an
aggregate or other SET operation.". This is caused by an aggregate
function (for example: MIN, MAX, SUM, AVG, COUNT) called with an
expression which contains NULL values. For example, consider the
following query in Northwind:
SELECT AVG(DATEDIFF(d,OrderDate,ShippedDate)) FROM Orders
To avoid the warning, the query can be rewritten as:
SELECT AVG(DATEDIFF(d,OrderDate,ShippedDate)) FROM Orders
WHERE ShippedDate IS NOT NULL
Razvan
--CELKO--
Yes; and what is your point? You are probably getting wrong answers,
destroying data integrity and your results will not port to DW or
other systems.
What is the cost of all that failure? If you worked for a drug
company and found that a recall would require literally hundreds of
bottles od drugs to be replaced, would you do it or not?
>> In the future, I will recommend writing all of our queries differently to protect against null values but for now what can I do? <<
Recommend it now, start working or it will not get done. There is
nothing more permanent than a kludge that can squeeze us by until
someone else has to maintain the mess.
I would start with your DDL to see if you have too many NULL-able
columns. Your data dictionary should explain the semanatics of each
NULL in the columns that require NULLs, so you can start there (ahhh..
you do have a data dictionary, don't you?).
Then start adding CHECK() constraints to prevent division by zero,
garbage in strings, etc.
After the DDL is clean, then go to the DML and look for generated
NULLs and other problems. My expereience is that fixing the DDL is
80% or more of the battle.
Laurie Murphy
which we currently do not handle correctly in our application.
I'm not sure why setting ANSI Warnings off at the database level is a bad
thing. Our application is not using indexed views, computed columns or
heterogenous queries. Most of our aggregate functions are min and max dates
for which nulls do not cause irregular or incorrect results.
In response to Celko's recommendations, I am a developer not a DBA, so much
of what you say, although it makes sense, is outside my domain. I will
certainly keep your suggestions in mind for future projects but I don't
believe our data has been compromised to the degree you suggest.
Erland Sommarskog
> Razvan is right, The warnings are causing ADO to generate empty recordsets
> which we currently do not handle correctly in our application.
Did you try using SET NOCOUNT ON?
> I'm not sure why setting ANSI Warnings off at the database level is a bad
> thing. Our application is not using indexed views, computed columns or
> heterogenous queries.
Not today, but maybe tomorrow.
Out of legacy we run our application with SET ANSI_WARNINGS OFF. This
has more than once been detrimental as division-by-zero and overflow
errors have gone unnoticed, causing more serious problems along the
road.
Laurie Murphy
set back to ON? Do you think ADO is doing this behind the scenes or is there
some way I can control it (besides those things already mentioned like
changing the connection string)?
I really do understand what you all are saying. I have an app scheduled to
go in next week and it's causing problems for the source system my app
depends on. The recompiles are killing us. It really isn't feasible at this
time to change the way we've been doing things for the last 5 years. It's
possible someone will have time to rewrite all of these to be compliant with
standards, but this would require a complete retesting of 2 applications, not
something we have the resources for right now. Like I said before, we are not
doing any mathematical calculations in this app, not even avg. Min and Max
for dates is about it.
I do appreciate the time you all have devoted to my question. I am at my
wit's end.
Laurie Murphy
beautifully. The problem is, I am working on procedures that take information
from an application via triggers to build audit tables. The application is
currently working fine and has been in production for 4 years. Using your
suggestion requires a change to this application and would require major
retesting to make sure it doesn't break anything. Since my job is supposed to
go live next week, I was looking for a simpler solution (and one with no
impact on the source system) such as how can I keep the warnings off. I
believe it is happening when I call recordset.open but I cannot be sure.
My initial question has never been answered, but this may be as close as I
get.
Razvan Socol
regardless of the the database options or the default connection
options set at the server-level. Therefore, if you need the
ANSI_WARNINGS option to be OFF and you don't want to change it in
every SP, you can change it immediately after you open your
connection, by executing:
cn.Execute "SET ANSI_WARNINGS OFF"
This setting should remain in effect for that connection (until a
different SET ANSI_WARNINGS is encountered or until the connection is
closed). However, if you execute any SET option in a stored procedure,
it will remain in effect only for the duration of that SP (and of
other SP-s called by it) and it will revert to the previous value of
that option when the SP ends.
Razvan
Laurie Murphy
However, in one function, it uses the recordset.open with the sp name, and
connection string. I have the ansinpw=no in the connection string but somehow
I am still getting the warnings .
Thank you so much Razvan, I'm almost there!
Razvan Socol
<LaurieMur...@discussions.microsoft.com> wrote:
> Yes, I have done that for all of the functions that open a connection object.
> However, in one function, it uses the recordset.open with the sp name, and
> connection string. I have the ansinpw=no in the connection string but somehow
> I am still getting the warnings .
Well, then change it to open a connection first, execute SET
ANSI_WARNINGS, then open the recordset with the connection object
instead of the connection string.
Razvan