Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

What does "SET XACT_ABORT ON" mean? Expand / Collapse
Author
Message
Posted Tuesday, April 16, 2013 7:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 306, Visits: 579
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?
Post #1442746
Posted Tuesday, April 16, 2013 8:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:07 AM
Points: 12,876, Visits: 31,790
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1442756
Posted Tuesday, April 16, 2013 8:13 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 306, Visits: 579
So, in my understading that "SET XACT_ABORT ON" should add all time.
Post #1442764
Posted Tuesday, April 16, 2013 8:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:50 PM
Points: 13,062, Visits: 11,891
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 Moden's 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)
Post #1442767
Posted Tuesday, April 16, 2013 9:09 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 306, Visits: 579
I call this stored procedure from one asp.net app.
If transaction roll back, does app catch an error message?
Post #1442796
Posted Tuesday, April 16, 2013 9:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:50 PM
Points: 13,062, Visits: 11,891
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 Moden's 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)
Post #1442800
Posted Tuesday, April 16, 2013 9:58 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:54 PM
Points: 42,434, Visits: 35,488
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 2008, MVP
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

Post #1442831
Posted Tuesday, April 16, 2013 10:16 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 306, Visits: 579
It means that just keep "BEGIN TRAN" without "SET XACT_ABORT ON" if I want to roll back if any error occurs?



Post #1442842
Posted Tuesday, April 16, 2013 11:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:54 PM
Points: 42,434, Visits: 35,488
http://sqlinthewild.co.za/index.php/2011/05/17/on-transactions-errors-and-rollbacks/


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1442883
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse