SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What does "SET XACT_ABORT ON" mean?


What does "SET XACT_ABORT ON" mean?

Author
Message
adonetok
adonetok
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1740 Visits: 819
I created a transaction stored procedure adding a line as

Begin Tran

But someone told me that need to modify as

SET XACT_ABORT ON
BEGIN TRAN

Is that true? What does "SET XACT_ABORT ON" mean?
Lowell
Lowell
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75878 Visits: 40999
it means that if you start a BEGIN TRAN,
if ANY error occurs in the transaction, instead of moving on to the next statement and continuing to process(the default behavior in SSMS without a transaction), it stops processing and immediately rolls back the transactions.

this is ideal for situations where you need to do multiple steps, and it's an "all or nothing" kind of transaction.

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
adonetok
adonetok
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1740 Visits: 819
So, in my understading that "SET XACT_ABORT ON" should add all time.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65464 Visits: 17980
adonetok (4/16/2013)
So, in my understading that "SET XACT_ABORT ON" should add all time.


As with everything in sql there are NO absolutes. If you want a portion of a process to be able to complete even if there are errors in other steps then you would not want to do this.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
adonetok
adonetok
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1740 Visits: 819
I call this stored procedure from one asp.net app.
If transaction roll back, does app catch an error message?
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65464 Visits: 17980
adonetok (4/16/2013)
I call this stored procedure from one asp.net app.
If transaction roll back, does app catch an error message?


That depends on what the stored proc does. Does the proc throw an exception? If so, then it would be returned to your page. If the proc handles the error then it may not be returned. You would have to post a bit more info to provide a definite answer.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)

Group: General Forum Members
Points: 235326 Visits: 46376
adonetok (4/16/2013)
So, in my understading that "SET XACT_ABORT ON" should add all time.


If you want SQL to automatically roll back transactions in case of an error. Personally it's a setting that I strongly prefer to be off.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


adonetok
adonetok
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1740 Visits: 819
It means that just keep "BEGIN TRAN" without "SET XACT_ABORT ON" if I want to roll back if any error occurs?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)

Group: General Forum Members
Points: 235326 Visits: 46376
http://sqlinthewild.co.za/index.php/2011/05/17/on-transactions-errors-and-rollbacks/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


webrunner
webrunner
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8213 Visits: 4003


Thanks, Gail - that was a great explanatory article!

FYI I did notice that one set of code in that article seems to a couple of typos:

CREATE TABLE TestingTransactionRollbacks (
<pre> ID INT NOT NULL
PRIMARY KEY ,
SomeDate DATETIME DEFAULT GETDATE()
) ;
GO

BEGIN TRANSACTION
BEGIN TRY
-- succeeds
INSERT INTO TestingTransactionRollbacks (ID)
VALUES (1)
-- Fails. Cannot insert null into a non-null column
INSERT INTO TestingTransactionRollbacks (ID)
VALUES (NULL)
-- succeeds
INSERT INTO TestingTransactionRollbacks (ID)
VALUES (2)
-- fails. Duplicate key
INSERT INTO TestingTransactionRollbacks (ID)
VALUES (2)
-- succeeds
INSERT INTO TestingTransactionRollbacks (ID)
VALUES (3)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS Severity, ERROR_MESSAGE() AS ErrorMessage, ERROR_LINE() AS ErrorLine, ERROR_PROCEDURE() AS ErrorProcedure
END CATCH
GO
EXEC InsertWithError

GO
DROP TABLE TestingTransactionRollbacks
DROP PROCEDURE InsertWithError

The first typo seems to be an artifact of the "pre" html display code. The second, perhaps, is because maybe the code originally was wrapped in a CREATE PROCEDURE InsertWithError or something like that.

Thanks again, though! I want to come up with a basic error handling framework that I can re-use when creating procedures, and I finally want to get a better grasp of transactions and rollbacks to do so.

- webrunner

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search