ANSI Options Part 4 - ANSI_WARNINGS
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/ansioptionspart4ansi_warnings.asp
-
Just wanted to clarify one thing:
The transaction is not rolled back unless you have xact_abort also set to ON. Only the statement that caused the error is rolled back.
To illustrate this point, consider the following example:
create table mytab (a int)
begin tran
insert mytab values (1)
update mytab set a=a/0
commit tran
select * from mytab
Results:
(1 row(s) affected)
Server: Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
The statement has been terminated.
a
-----------
1
(1 row(s) affected)
-
Steve, just wanted to highlight some issues with this article:
SET ANSI_WARNINGS has nothing to do with "Divide by zero" errors. So, it will never suppress that error by returning a NULL. For example:
--This one fails
SET ANSI_WARNINGS ON
SELECT 1/0
--This one fails too
SET ANSI_WARNINGS OFF
SELECT 1/0
However, if you use SET ARITHABORT OFF, that will suppress the "Divide by zero" error and returns a NULL. But SET ARITHABORT is not one of the ANSI options.
--This one fails
SET ARITHABORT ON
SELECT 1/0
--This one returns NULL instead of erroring out
SET ARITHABORT OFF
SELECT 1/0
Also, the transaction is never rolled back in case of a "Divide by zero" error, unless you have SET XACT_ABORT ON.
HTH,
Vyas
HTH,
Vyas
SQL Server MVP
http://vyaskn.tripod.com/ -
From BOL:
Effect of ANSI_WARNINGS Setting
The ANSI_WARNINGS setting affect query processor behavior despite the current settings of ARITHABORT and ARITHIGNORE.
For example, even if SET ARITHABORT or SET ARITHIGNORE is OFF, if SET ANSI_WARNINGS is ON, SQL Server still returns an error message when encountering divide-by-zero or overflow errors.
This table summarizes the behavior.
ARITHABORT ANSI_WARNINGS Behavior
ON ON Abort statement only.
ON OFF Abort batch.
OFF ON Abort statement only.
OFF OFF Continue; value is NULL.
-
Thanks for the updates. I neglected to test this and didn't see an errata on this.
Steve Jones
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply