CODEWITHSUNDEEP
sqlsql-servertransactionssql-server-2008-r2
Rise_against
Rise_against

Reputation: 1070

C# SQL transaction

I am using a C# class that is calling a SQL stored procedure in a serializable transaction.

So if something goes wrong in the stored procedure, everything is rolled back.

I have one statement in the SQL stored procedure that should be always executed (even if the stored procedure fails at some point and a rollback occurs). The statement is an update of a record.

I cannot change the C# library, so I need to do this in my stored procedure.

Is there some way I can execute that one statement outside the transaction?

Upvotes: 0

Views: 363

Answers (3)

andersh
andersh

Reputation: 8383

You could perhaps use SAVE TRANSACTION. It is not supported in distributed transactions, and your statement must be executed first, so it might not be what you are looking for.

Upvotes: 1

Jānis
Jānis

Reputation: 2266

Your solution does not sound like a good one.. For example- if your stored procedure will be part of bigger transaction, then it will commit all changes made before it. Also I believe no one would guess that your proc has such behaviour without first seeing code.

The need to always execute some part of proc sounds like need for security audit. So maybe you should use trace, extended events or sql server audit instead.

if you really need what you say you need- you can use method described here: How to create an autonomous transaction in SQL Server 2008

Upvotes: 0

Rise_against
Rise_against

Reputation: 1070

I have found the solution. I didn't realize that SQL knew it was called in a transactional matter by the c# class.

The update statement that should always be executed is the first step (and also last step) in the procedure. Let me clarify: I have an IF function. If it is true, the update should occur no matter what. If it is false, some transactional logic should be executed. Now in the c# class, it expects a result from the stored proc. If the proc doesn't return a result (like it does in the update statement), it rollbacks the transaction.

So by just adding the following lines right after the update statement, the update occurs :)

IF @@TRANCOUNT > 0
    BEGIN
        COMMIT TRANSACTION
    END

Upvotes: 0

Related Questions