But I need to commit some data in every case. So I need to create a second transaction which is independent from the calling transaction.
What is the best way to do this inside a CLR procedure?
Thanx a lot
Hi!
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.).
|||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.|||
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()
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
|||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
