Accessing the Current Transaction in BOL (April 05')

Here is the text I do not believe is correct or at the least misleading...

"If you want to terminate the external transaction from within your stored procedure or function. In this case, you call the Transaction.Current.Rollback method. "

You cannot terminate transactions declared in TSQL in SQLCLR routines.

Derek-

Thank you for pointing out this. We will work to update this BOL article!

It is possible to terminate transactions declared within TSQL within SQLCLR routines, but it is recommended to manipulate them through the use of System.Transactions.TransactionScope. For example, this would rollback the current transaction:

using(TransactionScope transactionScope = new TransactionScope(TransactionScopeOptions.Required))

{

}

The TransactionScopeOptions.Required parameter specifies that the transaction scope should be scoped to the current active transaction if one exists, otherwise start a new transaction.

Invoke transactionScope.Complete() if you would like to complete the work here within the transaction scope. If you omit the transactionScope.Complete(), the transaction will be rolled back. A common use case would be to perform some work and then call transaction.Complete(). This pattern allows for an exception to be raised preventing transaction.Complete() from being invoked and therefore the transaction would be rolled back.

It is recommended to use TransactionScopes for the interaction between CLR and TSQL. I would recommend reading the TransactionScope MSDN articles for more information.

Thanks!

Jason Stowe (MS)

|||

Hi jason, yes the scope class works...but the following does not and if I were not aware of it its confusing:

begin tran

exec usp_SQLCLRProc

commit tran

c#:

if(some condition is true){System.Transactions.Transaction.Current.Rollback;}