How to set "SET XACT_ABORT ON " in a SQL Server transaction?
sql-server, sql-server-2008, stored-procedures, t-sql, transactions
Solution
You normally set `xact_abort` as part of the body of the stored procedure:
CREATE PROCEDURE MyProc
AS
SET XACT_ABORT ON
BEGIN TRAN
....
There are two "special" settings that are remembered from the session that created the procedure. Explanation from MSDN:
Stored procedures execute with the SET settings specified at execute time except for SET ANSI_NULLS and SET QUOTED_IDENTIFIER. Stored procedures specifying SET ANSI_NULLS or SET QUOTED_IDENTIFIER use the setting specified at stored procedure creation time. If used inside a stored procedure, any SET setting is ignored.
So when you create a stored procedure, SQL Server copies the QUOTED_IDENTIFIER option from the connection to the procedure definition. The goal is that someone else with a different QUOTED_IDENTIFIER setting still gets the behavior the author of the procedure intended.
The same is not true for `XACT_ABORT`.
Problem
I want to set `SET XACT_ABORT ON` in a SQL Server 2008R2 stored procedure with a transaction, so do it in a creation script: ``` SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET XACT_ABORT ON GO CREATE PROCEDURE MyProc AS BEGIN TRAN ... IF @@ERROR <> 0 BEGIN GOTO Done END ... IF @@ERROR <> 0 BEGIN GOTO Done END COMMIT TRAN Done: IF @@ERROR <> 0 BEGIN ROLLBACK TRAN END GO ``` After successful creation, I check the transaction by clicking "Modify" stored procedure option and in a generated `ALTER PROCEDURE` script I don't see `SET XACT_ABORT ON` line: ``` SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE MyProc AS BEGIN TRAN ... ``` Where am I wrong or what is the trick? How to correctly define `SET XACT_ABORT ON`?