1

I have a stored procedure that is called from a c# application.
The transaction is started and commited/rolledback from this c# application.

The stored procedure can do some inserts/updates in various tables, which will all be commited or rolledback by the calling application.

The problem is that the stored procedure also insert records into a logtable, which must survive the rollback.

What is the best way of doing this ?

I think I remember from a company I worked for long ago they had solved this by creating a stored procedure for the logging, and this stored procedure had some exotic statements that made it work outside the transaction, something like that. As I said, long time ago I could remember this wrong.

Mohit Kanwar
  • 2,962
  • 7
  • 39
  • 59
GuidoG
  • 11,359
  • 6
  • 44
  • 79
  • there is a statement called [Save transaction](https://msdn.microsoft.com/en-us/library/ms188378.aspx), perhaps that's what you are looking for. – Zohar Peled Jul 30 '15 at 09:02
  • I do not see how save transaction can help me with this question ? – GuidoG Jul 30 '15 at 09:07
  • that's the only way I know how to rollback only a part of the transaction. Though I've never used that, my educated guess would be to nest another transaction that will insert data to the log table, and call save transaction on that. after that, any rollback on the top transaction should not roll back the data inserted to the log table. Again, it's an educated guess only, I have no experience with this statement. – Zohar Peled Jul 30 '15 at 09:10
  • nested transaction does not work, sql server does not support autonomous transactions. The rollback of the outer transaction will still rollback the inner transaction even if this one commited. – GuidoG Jul 30 '15 at 09:17
  • I also do not want to rollback part of a transaction, the transaction must commit / rollback everthing. I am looking for a way to insert records to a log table not affected by this transaction – GuidoG Jul 30 '15 at 09:18
  • sql server will commit or rollback inner transactions when the containing transactions are commited or rolled back. that's true. the point of the inner transaction is so that the save transaction statement inside it will only save the insert into log table statement. at least, that my opinion. I can't really test it now, but I'm guessing you can... – Zohar Peled Jul 30 '15 at 09:24
  • I see, the rollback of the outer transaction will rollback the inner transaction, but it might be possible that a save transaction on the inner transaction might survive the rollback of the outer transaction. That is something I can test indeed – GuidoG Jul 30 '15 at 09:28
  • No, it cannot help me. save transaction does not updates any tables but just gives a savepoint that I can use with the rollback statement. This would still require a partial rollback which is not what I want – GuidoG Jul 30 '15 at 09:38
  • Apparently you are correct. that doesn't help. – Zohar Peled Jul 30 '15 at 11:49

1 Answers1

1

Some times ago, I've develop a tools that logged stored procedure execution in a databases table. The tools was written as a C# assembly compiled into the Database Server and based on differents SQL procedures and functions linked to its C# entry points.

To allow a rollback without the lost of all events allready logged, the C# assembly SHOULD used a full defined connectionString to connect to its database server (SERVERNAME\INSTANCE server param instead of local).

This is perhaps the solution used by your previous company.

Meanwhile, there are some disadvantages:

  • thoses connections was qualified as "external" and the "truthfully" databases parameters should be set to true to allow code execution if not signed
  • this solution is not supported on clouded databases (AWS RDS or Azure)
  • A new connection is created by C# methods

For this last reason and a customer need, I've rewrite a toolbox based on 100% T-SQL source code.

I've just write a response which can be usefull see: https://stackoverflow.com/a/32988757/1183297

Community
  • 1
  • 1
Khonsort
  • 483
  • 4
  • 6