0

We have a system that has both a .NET and SQLServer component. The .NET component is responsible to start the process and enclose the calls to stored procedures in a transaction.

That process is done using mostly stored procedures on the database, which all write to our own log table.

Now, the problem is that the transaction, being started in the .NET code, will be enclosing all others that are used in the database code, and if one of the child transactions rolls back the log table is gone.

Is there any way to keep the logs, rolling back everything else?

Thanks in advance.

Improve this question

2 Answers 2

Reset to default
2

Use a table variable to store the error: this is unaffected by transactions

Or use SET XACT_ABORT ON in the procs which implicitly rolls back and log after error

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I haven't forgotten about this, i will reply once i have tested your solution.
0

Is Transaction SavePoints can help you?

This is exactly data base level solution:

Savepoints offer a mechanism to roll back portions of transactions. You create a savepoint using the SAVE TRANSACTION savepoint_name statement. Later, you execute a ROLLBACK TRANSACTION savepoint_name statement to roll back to the savepoint instead of rolling back to the start of the transaction.

Also you can reffer to SqlTransaction.Save Method for a C# solution, hope this is exatcly what you are looking for:

Creates a savepoint in the transaction that can be used to roll back a part of the transaction, and specifies the savepoint name.

Improve this answer

1 Comment

I haven't forgotten about this, i will reply once i have tested your solution.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.