Upgrade your Internet Experience
Microsoft.com
Welcome Sign in
SQL Server Developer Center
Click to Rate and Give Feedback

  Switch on low bandwidth view
Community Content
In this section
Statistics Annotations (0)
SQL Server 2005 Books Online (November 2008)
Accessing the Current Transaction

If a transaction is active at the point at which common language runtime (CLR) code running on Microsoft SQL Server is entered, the transaction is exposed through the System.Transactions.Transaction class. The Transaction.Current property is used to access the current transaction. In most cases it is not necessary to access the transaction explicitly. For database connections, ADO.NET checks Transaction.Current automatically when the Connection.Open method is called, and transparently enlists the connection in that transaction (unless the Enlist keyword is set to false in the connection string).

You might want to use the Transaction object directly:

  • If you want to enlist a resource that does not do automatic enlistment, or for some reason was not enlisted during initialization.
  • If you want to explicitly enlist a resource in the transaction.
  • 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. The rest of this topic describes other ways to cancel an external transaction.

You can cancel external transactions from a managed procedure or function in the following ways:

  • The managed procedure or function can return a value by using an output parameter. The calling Transact-SQL procedure can check the returned value and, if desired, execute ROLLBACK TRANSACTION.
  • The managed procedure or function can throw a custom exception. The calling Transact-SQL procedure can catch the exception thrown by the managed procedure or function in a try/catch block and execute ROLLBACK TRANSACTION.
  • The managed procedure or function can cancel the current transaction by calling the Transaction.Rollback method if a certain condition is met.

When called within a managed procedure or function, the Transaction.Rollback method throws an exception with an ambiguous error message and can be wrapped in a try/catch block. The error message you see is similar to the following:

Msg 3994, Level 16, State 1, Procedure uspRollbackFromProc, Line 0
Transaction is not allowed to roll back inside a user defined routine, trigger or aggregate because the transaction is not started in that CLR level. Change application logic to enforce strict transaction nesting.

This exception is expected and the try/catch block is necessary for code execution to continue. Without the try/catch block, the exception will be immediately thrown to the calling Transact-SQL procedure and managed code execution will finish. When the managed code finishes execution, another exception is raised 

Msg 3991, Level 16, State 1, Procedure uspRollbackFromProc, Line 1 
The context transaction which was active before entering user defined routine, trigger or aggregate " uspRollbackFromProc " has been ended inside of it, which is not allowed. Change application logic to enforce strict transaction nesting. The statement has been terminated.

This exception is also expected, and for execution to continue, you must have a try/catch block around the Transact-SQL statement that performs the action that fires the trigger. Despite the two exceptions thrown, the transaction is rolled back and the changes are not committed.

Example

The following is an example of a transaction being rolled back from a managed procedure using the Transaction.Rollback method. Notice the try/catch block around the Transaction.Rollback method in the managed code. The Transact-SQL script creates an assembly and managed stored procedure. Note that the EXEC uspRollbackFromProc statement is wrapped in a try/catch block so that the exception thrown when the managed procedure finishes execution is caught.

C#

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Transactions;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void uspRollbackFromProc()
{
   using (SqlConnection connection = new SqlConnection(@"context connection=true"))
   {
      // Open the connection.
      connection.Open();

      bool successCondition = true;

      // Success condition is met.
      if (successCondition)
      {
         SqlContext.Pipe.Send("Success condition met in procedure."); 
         // Perform other actions here.
      }
            
      //  Success condition is not met, the transaction will be rolled back.
      else
      {
         SqlContext.Pipe.Send("Success condition not met in managed procedure. Transaction rolling back...");
         try
         {
               // Get the current transaction and roll it back.
               Transaction trans = Transaction.Current;
               trans.Rollback();
         }
         catch (SqlException ex)
         {
            // Catch the expected exception. 
            // This allows the connection to close properly.                    
         }  
      }

      // Close the connection.
      connection.Close();
   }
}
};

Visual Basic

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Transactions

Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub  uspRollbackFromProc ()
   Using connection As New SqlConnection("context connection=true")

   ' Open the connection.
   connection.Open()

   Dim successCondition As Boolean
   successCondition = False

   ' Success condition is met.
   If successCondition Then

      SqlContext.Pipe.Send("Success condition met in procedure.")

      ' Success condition is not met, the transaction will be rolled back.

   Else
      SqlContext.Pipe.Send("Success condition not met in managed procedure. Transaction rolling back...")
      Try
         ' Get the current transaction and roll it back.
         Dim trans As Transaction
         trans = Transaction.Current
         trans.Rollback()

      Catch ex As SqlException
         ' Catch the exception instead of throwing it.  
         ' This allows the connection to close properly.                    
      End Try

   End If

   ' Close the connection.
   connection.Close()

End Using
End Sub
End Class

Transact-SQL

--Register assembly.
CREATE ASSEMBLY TestProcs FROM 'C:\Programming\TestProcs.dll' 
Go
CREATE PROCEDURE uspRollbackFromProc AS EXTERNAL NAME TestProcs.StoredProcedures.uspRollbackFromProc
Go

-- Execute procedure.
BEGIN TRY
BEGIN TRANSACTION 
-- Perform other actions.
Exec uspRollbackFromProc
-- Perform other actions.
PRINT N'Commiting transaction...'
COMMIT TRANSACTION
END TRY

BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNum, ERROR_MESSAGE() AS ErrorMessage
PRINT N'Exception thrown, rolling back transaction.'
ROLLBACK TRANSACTION
PRINT N'Transaction rolled back.' 
END CATCH
Go

-- Clean up.
DROP Procedure uspRollbackFromProc;
Go
DROP ASSEMBLY TestProcs;
Go

Release History

12 December 2006

New content:
  • Added the Canceling an External Transaction section.
Tags What's this?: Add a tag
Community Content   What is Community Content?
Add new content RSS  Annotations
.NET Stored Procedures and Transactions Support      Enkee ... dbowen   |   Edit   |   Show History
How to use internal transactions in clr code (I'm using c#) with context connection (my expirience):

1. Start the global transaction somewhere in the begining: SqlTransaction clrTransaction = connection.BeginTransaction()
2. Begin your local named transaction with clrTransaction.Save("mytran"); Use your own transaction name instead of "mytran".
3. If something wrong, rollback it with clrTransaction.Rollback("mytran") or Commit("mytran") if all ok;
4. At the end of you procedure make clrTransaction.Commit();

You can't use Transaction.Current if BEGIN TRANSACTION statement does not exists in t-sql code before calling your clr procedure
Transaction.Current does NOT support Save(), so you can only rollback everything that was made from the last t-sql BEGIN TRANSACTION.
In other words you cant make nested transactions with Transaction.Current.

If you try to make Transaction.Current.Rollback() as in expamples above, you will get [Transaction is not allowed to roll back inside a user defined routine...] rigth ater Rollback() or [The context transaction which was active before entering user defined routine..] after exiting from except block.
And of course server will show an error that transactions count in yours clr sp changed, then will make rollback.
Flag as ContentBug
© 2009 Microsoft Corporation. All rights reserved. Terms of Use  |  Trademarks  |  Privacy Statement
Page view tracker