Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

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: Tuesday, August 2, 2016 1:56 PM
Points: 361, Visits: 721
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: Thursday, September 22, 2016 4:54 AM
Points: 14,468, Visits: 38,065
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!
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: Tuesday, August 2, 2016 1:56 PM
Points: 361, Visits: 721
So, in my understading that "SET XACT_ABORT ON" should add all time.
Post #1442764
Posted Tuesday, April 16, 2013 8:15 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 2:51 PM
Points: 15,981, Visits: 16,517
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: Tuesday, August 2, 2016 1:56 PM
Points: 361, Visits: 721
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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 2:51 PM
Points: 15,981, Visits: 16,517
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: Today @ 4:59 AM
Points: 45,296, Visits: 43,511
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

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: Tuesday, August 2, 2016 1:56 PM
Points: 361, Visits: 721
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: Today @ 4:59 AM
Points: 45,296, Visits: 43,511
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

Post #1442883
Posted Friday, September 18, 2015 3:08 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:46 AM
Points: 2,904, Visits: 3,646


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
Post #1721023
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse