YAPA ANSI Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

YAPA ANSI Error

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-18 : 15:45:27

Yet another post about ANSI ERRORS

Is is a bad programming practice to generate ANSI ERRORS when NULLs are elminated in aggregate functions and simply ignore the warnings in ASP?

I've been doing this for a while, then today, my check for the warning changed from:

NativeError 0 , Number 265946

to

NativeError 8153, Number 265946

Is there a better way to test for warnings vs errors in ASP or should I recode my stored procedures to eliminate the warnings?

Sam

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-18 : 15:48:15

Best thing to do is to make sure the SPs don't generate the warnings.
It's usually because an aggregate isn't dealing with nulls

select sum(coalesce(amt,0)) from tbl
select sum(amt) from tbl where amt is not null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-18 : 16:03:26

Thanks Nigel.

I may go forward and rewrite my SPs, but it's conveniently terse to use COUNT(X) and SUM(X) when they behave exactly the way I need them to behave eliminating NULLs.

Reprogramming COUNT(X) as SUM(CASE WHEN X IS NULL THEN 0 ELSE 1 END) may be necessary but it's result is not as readable.

Any other thoughts about this? Does everyone avoid ANSI errors by programming alternative solutions to COUNT and SUM? Is there a better test in ASP for warnings? Is the warning generating overhead?

I'd try the SET ANSI_WARNINGS OFF, but there were some remarks in BOL about it causing problems: If SET ANSI_WARNINGS is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. -- This doesn't make me want to embrace SET ANSI_WARNINGS OFF as a general approach to solving this problem.

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-20 : 14:20:06

Is it the consensus of everyone that programming queries to avoide the null/aggregate warnings is the way to go?

Anyone here just handling the warning in ASP or using SET ANSI_WARNINGS OFF?

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources