XACT_ABORT OFF, ANSI_WARNINGS OFF, transaction rolled back upon first error unexpectedly - by Ansssss

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


2
0
Sign in
to vote
ID 796779 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 8/8/2013 7:39:03 AM
Access Restriction Public

Description

When XACT_ABORT=OFF and ANSI_WARNINGS=OFF, a transaction is rolled back upon the first statement that has an error, rather than continuing to process the remaining statements in the transaction. If I do the exact same thing, but this time with ANSI_WARNINGS=ON, the transaction processing continues. 

The documentation for XACT_ABORT (http://technet.microsoft.com/en-us/library/ms188792.aspx) says the only reason that a transaction abort may occur when XACT_ABORT=OFF is due to the severity level of the error, but the error raised has the same severity level regardless of the ANSI_WARNINGS setting. So the transaction rollback should not be occurring when XACT_ABORT=OFF and ANSI_WARNINGS=OFF.

Code to reproduce:

--------do this as sa---------
CREATE TABLE Junk( Name varchar(12) )
INSERT INTO Junk(Name) VALUES ('original')
DENY INSERT ON Junk to dbUser
---------------------------------

--now do the next to as dbUser

-----update is not attempted (but should be since XACT_ABORT is OFF)----
SET XACT_ABORT OFF
SET ANSI_WARNINGS OFF
BEGIN TRAN
INSERT INTO Junk(Name) VALUES ('new')
update Junk SET Name = 'updated' where Name = 'original'
COMMIT TRAN
--------------------------------

--------update is done------
SET XACT_ABORT OFF
SET ANSI_WARNINGS ON
BEGIN TRAN
INSERT INTO Junk(Name) VALUES ('new')
update Junk SET Name = 'updated' where Name = 'original'
COMMIT TRAN
-----------------------------
Sign in to post a comment.
Posted by Jos [MSFT] on 5/8/2014 at 3:01 PM
This behavior is by design. When ANSI_WARNINGS is OFF, permission violations cause transaction abort. We will work on getting this behavior documented.

We recommend to always set ANSI_WARNINGS to ON. We only support ansi_warnings off for back-compat reasons, and any new apps should use ANSI WARNINGS ON.


Thanks,
Jos de Bruijn - SQL Server PM
Posted by Manbeen [MSFT] on 11/1/2013 at 10:57 AM
Thank you for submitting this feedback. We will evaluate this issue and get back to you. Thanks again for your continued support in improving our product.
Posted by Erland Sommarskog on 8/29/2013 at 12:46 PM
The behaviour is obviously by design - it works the same way in SQL 6.5, I just tested.

However, this is a doc bug. There is no mention of this difference in the behvaiour for ANSI_WARNINGS. That is, a permission error is batch- and transaction aborting when ANSI_WARNINGS is OFF, but not if ANSI_WARNINGS is ON (unless XACT_ABORT is also ON).

A confession: I had no idea about this meaning for ANSI_WARNINGS until I by chance stumbled on this Connect item!