Is it possible to exclude a stored procedure from a transaction in MS SQL?

Question

To my problem: I call a stored procedure from my business code. This call is in an explicit transaction. A stored procedure sometimes calls another to write something to the database. This data should remain in the database even when the transaction is rolled back. A similar scenario is when you want to write something in the log table, and the log message should be saved (this is not my case, it is just a similar requirement).

How can I exclude the second stored procedure from an external transaction? I think I'm looking for something like "offline transactions" in Oracle. I was looking for a possible emulation, but all the solutions didn’t look very “pretty” (create a loopback server, add some .NET methods, ...)

Any ideas? Thanks!

Answer 1

There is no such elegant solution to this type of problem, although this seems to be a common occurrence. Everything between begin transaction and commit or rollback is complete. You cannot just insert a row into the log table, for example, and save it after a possible rollback .

But you can do some tricks.

1) Call your procedure with xp_cmdshell to call OSQL.exe. Performance will be poor, but external commands are not involved in the transaction, and nothing prevents you from executing external SQL queries.

2) in the stored procedure, you can add entries to the variable table instead of the real table. Table variables do not participate in the transaction because they do not modify the database. After that, add the contents of the variable to your table when you closed the transaction anyway.

3) If you cannot change the internal procedure, you can extract the records (records) that it may have created into the table variable after , but from within an open transaction. Rollback the transaction and add the extracted records to the table again.

Answer 2

As far as I know, you cannot exclude certain operations from transactions that contain them. My approach would be to try to explicitly do what you want to do, instead of rolling back the whole transaction. Or, if this is an option, roll back the transaction, and then re-run the stored procedure that you do not want to roll back.