Stack Overflow
Stack Overflow

Questions tagged [xact-abort]

Use this tag for questions related to XACT_ABORT, which is used for ensuring atomicity of transactions.

specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error, as discussed in SET XACT_ABORT (Transact-SQL).

usually accompanies this tag, so you might want to use it too.

22 questions
17
votes
1 answer

What is the scope of XACT_ABORT

What is the scope of a SET XACT_ABORT statement in SQL Server 2005? i.e.:begin-end block, procedure or trigger, connection, database, server?
Guillermo Gutiérrez
  • 17,273
  • 17
  • 89
  • 116
10
votes
1 answer

Why does SQL Server default XACT_ABORT to OFF? Can it be set to ON globally?

I understand the purpose of SET XACT_ABORT command: When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. When SET XACT_ABORT is OFF, in some cases only the…
HappyTown
  • 6,036
  • 8
  • 38
  • 51
9
votes
3 answers

How to use SET XACT_ABORT ON the right way

We have recently been parachuted to a new ETL project with very bad code. I have in my hands a query with 700 rows and all sort of update. I would like to debug it with SET XACT_ABORT ON; and the goal is to rollback everything if only one…
Francesco Mantovani
  • 10,216
  • 13
  • 73
  • 113
9
votes
2 answers

set xact_abort on and try catch together

i have a try catch block in my sp with just a insert statement in the try. the catch check error code if it is pk violation, if it is then do update. but some times i get "The current transaction cannot be committed and cannot support operations…
satuday
  • 165
  • 1
  • 1
  • 8
8
votes
2 answers

How to make SET XACT_ABORT ON rollback the transaction?

Based on the Books Online documentation of SET XACT_ABORT ON, i get the impression that if a T-SQL statement raises a run-time error, the entire transaction is terminated and rolled back: Remarks When SET XACT_ABORT is ON, if a Transact-SQL…
Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219
6
votes
2 answers

What is the effect of having XACT_ABORT on/off in parent/child stored procedures respectively?

I'm trying to improve the error handling of a current system to produce more meaningful error messages. I have a "root" stored procedure that makes several calls to other nested stored procedures. In the root sp, XACT_ABORT is set to ON but in the…
Will Weld
  • 318
  • 2
  • 6
  • 20
4
votes
2 answers

Transactions breaking when using SP_ExecuteSQL

I'm using SQLServer 2014 and I have a simple db with one table which has an ID and a varchar column called data. There's some weird behavior when I run the following statement: SET XACT_ABORT ON BEGIN TRANSACTION exec sp_executesql N'some…
Ivan Pintar
  • 1,861
  • 1
  • 15
  • 27
4
votes
1 answer

Are these code snippets equivalent ('set xact_abort on' vs 'try catch rollback')?

I used to use this code snippet within my stored procedure in SQL Server: create procedure proc_name --declare variables as set nocount on begin transaction begin try --do something commit transaction end try…
Anatoly U
  • 258
  • 1
  • 11
4
votes
1 answer

Transaction handling in Trigger (TRY/CATCH....XACT_ABORT ON)

I have the process scenario on SQL Server 2008R2: • A usp to gather data and then a transfer data between two SQL Servers This process is to be done with transaction at all levels of the process (usp, SSIS, and trigger) In the data flow…
Ramon Gonzalez
  • 241
  • 1
  • 4
  • 11
3
votes
1 answer

What happens here? SQL Server - XACT_ABORT ON + @@ERROR Checking . .

What happens with this type of scenario? SET XACT_ABORT ON BEGIN TRANSACTION ---DO SOMETHING HERE THAT CAUSES AN ERROR COMMIT TRANSACTION if @@error != 0 raiserror('SP failed. Step 7.', 20, -1) with log GO My guess is that because…
richard
  • 12,263
  • 23
  • 95
  • 151
3
votes
3 answers

SQL Server XACT_ABORT with exclusion

I have a larger stored procedure which utilizes several TRY/CATCH blocks in order to catch and log individual errors. I have also wrapped a transaction around the entire contents of the procedure, so as to be able to roll back the entire thing in…
Eli
  • 2,538
  • 1
  • 25
  • 36
2
votes
1 answer

Why SQL Server stored procedure selecting encrypted data into variable fails

Stored procedure that selects data from encrypted column (Always Encrypted) into variable fails with an error Cannot continue the execution because the session is in the kill state if XACT_ABORT is set to on. Removing SET XACT_ABORT ON; line makes…
Maxim
  • 23
  • 3
2
votes
2 answers

Transactions Rolling Back by default when XACT_ABORT is off

I have the following transaction BEGIN TRAN DECLARE @TransactionAmount MONEY = 5.00 UPDATE Account SET Balance -= @TransactionAmount WHERE AccountID = 1 UPDATE Account SET Balance += @TransactionAmount WHERE AccountID =…
SE1986
  • 2,534
  • 1
  • 10
  • 29
1
vote
1 answer

EF Core set XACT_ABORT for all database connections

I am using EF Core 3.1 to connect to SQL Server. I would like to SET XACT_ABORT ON for SQL Server on for all the connections I make in my application. Is there a hook on start up or context creation that I can run this? Because I am working with AWS…
l3utterfly
  • 2,106
  • 4
  • 32
  • 58
1
vote
0 answers

Trigger exits on first failed insert and cant set xact_abort OFF in SQL Server 2000

I have built a simple database to run on SQL Server 2005 and now need to get it working on SQL Server 2000. The aim is as follows: I receive a daily data feed containing ~5k records into a Staging table. When this insert is done a single record is…
LDM
  • 13
  • 3
1
2