Finally Clause in SQL Server Transaction? Something that will execute irrespective of success or failure?

sql sql-server transactions try-catch-finally sqltransaction
Programmer picture Programmer · Oct 24, 2012 · Viewed 11.3k times · Source

In SQL Server, is there something similar to finally clause in try..catch... block of c# ?

I mean, I am using BEGIN TRAN, END TRAN, COMMIT TRAN, ROLLBACK TRAN etc in a SQL Server transaction and want a section or some set of actions that needs to fire irrespective of success or failure or transaction.

Is there a solution for that? (similar to finally block on try/catch of OOPS languages).

Thanks in advance

Answer

Mike Sherrill 'Cat Recall' picture Mike Sherrill 'Cat Recall' · Oct 24, 2012

There isn't anything that will run "irrespective of success or failure" with 100% reliability. It doesn't matter whether you're talking about the "finally" clause of a C# program, or a BEGIN TRY...END TRY BEGIN CATCH...END CATCH stucture on SQL Server.

The problem is that terminal blocks like these can't execute under every possible failure mode. Every possible failure mode has to include software, hardware, and network failures. If your client is the government, it probably has to include missle attacks, too.

Obligatory reference to a classic TheDailyWTF.

Related questions

Correct use of transactions in SQL Server

I have 2 commands and need both of them executed correctly or none of them executed. So I think I need a transaction, but I don't know how to use it correctly. What's the problem with the following script? BEGIN TRANSACTION […

Read More
SQL Server - transactions roll back on error?

We have client app that is running some SQL on a SQL Server 2005 such as the following: BEGIN TRAN; INSERT INTO myTable (myColumns ...) VALUES (myValues ...); INSERT INTO myTable (myColumns ...) VALUES (myValues ...); INSERT INTO myTable (myColumns ...) VALUES (myValues ...); COMMIT TRAN; It …

Read More
Is there a way to list open transactions on SQL Server 2000 database?

Does anyone know of any way to list open transactions on SQL Server 2000 database? I am aware that I can query the view sys.dm_tran_session_transactions on SQL 2005 (and later) database versions, however this is not available on …

Read More