DML statements within the main transaction scope

Feb 1, 2016 at 3:30 PM
Howdy,

Thank you for sharing this with the community!

We were trying to implement a sproc based on your code in order to execute CLR code to populate some DB tables. The idea is to repopulate few tables from scratch by using batches while invoking the CLR code and at the same time running all in parallel. However we found out that it wasn't possible to include DML statements (simple truncate table) within the main named session.
We don't want to truncate the tables if we got any exception during the repopulation.

We were able to run the 'truncate table' statements in a initial multi thread session via dbo.AsyncName. Then waited for this to finish via AsyncWait. Followed by creating second named session for the population code. We could see the expected number of executions while running AsyncTaskStatus afterwards, but this doesn't show the first named session - just the second one with the total number of Completed Tasks (including the truncate). This is a bit confusing as we were expecting to see both named session with the number of executions within each one.

Is there any reason why the DML statements cannot be within the transaction isolation directly?

Cheers
-Z