CODEWITHSUNDEEP
sqlsql-servertransactions
Dib
Dib

Reputation: 2093

Is there a way to execute a single SQL statement from within a transaction block that is exclusive of the transaction

Is there a way to execute a single SQL Server T-SQL statement from within a transaction block that is excluded from the transaction?

For example, we want to write to a status table periodically during a batch process, that is within a transaction to advise how far through the process has been completed (see reason below), but if the batch process fails and the transaction is rolled back, the record written into the status table should remain intact.

Reason for writing into the status table as we go along is so that the progress through the batch can be monitored as the batch runs.

Upvotes: 0

Views: 58

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

What you are looking for has a name (at least in Oracle): autonomous transaction. If you Google "autonomous transaction sql server", you will get references such as this and this.

There are basically three approaches in SQL Server:

  1. Establish a new connection to the database for the transaction.
  2. Write the log records to a file rather than the database (files don't follow transactional semantics). You can later load the records from the file into a table, if that is desirable.
  3. Don't use explicit transactions, implementing "custom" roll-back in the case of failure.

Neither of these is fully satisfying, but they are viable work-arounds.

Upvotes: 2

Related Questions