Error create a second independent transaction inside a CLR stored procedure?

Error create a second independent transaction inside a CLR stored procedure?

• Imran Koradia

  Hi!

  < xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /> 

  Create another connection to the server and create a transaction on this second connection. Please note that since your main connection is inproc (“context connection=true” in the connection string), the second connection should be non-inproc (network. Of cause, you can connect to the same server.).

  
  
  

• Friction

  Hello,
  I made a small example to reproduce the error.
  
  CLR procedure
  <Microsoft.SqlServer.Server.SqlProcedure()> _

  Public Shared Sub Test(ByVal sConnectString As String)

  Dim t As SqlClient.SqlTransaction = Nothing

  Dim c As SqlClient.SqlConnection = Nothing

  c = New SqlClient.SqlConnection(sConnectString)

  c.Open()

  t = c.BeginTransaction()

  t.Commit()

  c.Close()

  End Sub
  
  T-SQL caller:
  begin tran
  execute Test 'Server=(local);database=cpwerx;user Id=sa;password=xyz'
  commit tran
  
  Error:
  A .NET Framework error occurred during execution of user defined routine or aggregate 'Cas':
  System.Data.SqlClient.SqlException: Transaction context in use by another session.
  System.Data.SqlClient.SqlException:
     at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
     at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
     at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
     at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj)
     at System.Data.SqlClient.SqlInternalConnectionTds.PropagateTransactionCookie(Byte[] cookie)
     at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
     at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
     at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
     at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
     at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
     at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
     at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
     at System.Data.SqlClient.SqlConnection.Open()
  
  

  
  

• Fantasiiio

  Hi!
  
  Modify your connection string so that it has "Enlist=false" in it. Otherwise the connection inherits current transaction.

  More on "Enlist" option is in doc on SqlConnection.ConnectionString property

  
  
  

• MechGuru

  Thank you,
  
  I tried this already. But I got an error when I call BeginTransaction on the second connection. When I remember correctly it as "Transcation already in use by another session". I call the procedure from t-sql inside a begin tran block
  
  Any idea about that
  
  I will try to build a small example an post it here.
  

• jcribbs

  Than you!

  We've been stuck for a week. We had a .Net CLR stored procedure that worked locally but, failed when invoked remotely. That connection string setting is exactly what we needed.

  -Joe

  
  
• Error create a second independent transaction inside a CLR stored procedure?