0

I have two databases on the same instance of SQL Server

In 'DatabaseA' I have a stored procedure which calls C# code (CLR assembly in SQL) to unpack data and after unpacking writes the unpacked data onto DatabaseB's tables (ScanTracking, Strings, Numerics)

The stored procedure when executed manually works just fine.

However when I run the stored procedure via trigger on a table present in DatabaseA. I get the following error

'Transaction Context is used by another session'

The full details of the error is as follows:

Msg 6522, Level 16, State 1, Procedure ExecuteDeSerializeBulk, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "ExecuteDeSerializeBulk": 
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, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   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, Boolean isDelegateControlRequest)
   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.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
   at System.Data.ProviderBase.DbConnectionPool.PrepareConnection(DbConnection owningObject, DbConnectionInternal obj, Transaction transaction)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnecti...
The statement has been terminated.

I did some research and found the following links

Transaction context in use by another session

https://learningintheopen.org/2014/01/21/technical-microsoft-sql-server-transact-sql-linked-server-error-transaction-context-in-use-by-another-session-msg-3910-level-16-state-2-line-1/

I am not using a Linked server or anything complicated, both my databases are on the same server.

As a workaround I reverted to SQL Agent job which runs every 5 minutes and unpacks data onto DatabaseB this works fine. However my mind is telling me that i am not finding a solution to the problem with the trigger and working around it using a SQL Agent job.

Any thoughts or suggestions will greatly help.

Abe
  • 1,879
  • 2
  • 24
  • 39
  • Is the session invoking the trigger MARS enabled? Are you using TransactionScope? – Dan Guzman Dec 01 '17 at 11:56
  • @mjwills - thanks I have updated the question for you. Let me know if you can make any more sense now with the full exception detail. Nice analogy by the way. I look forward for a suggestion from youself. – Abe Dec 01 '17 at 15:50
  • Please show us the source code for `ExecuteDeSerializeBulk`, the trigger, and the C# code that is updating the database (thus 'triggering the trigger'). – mjwills Dec 01 '17 at 20:48

1 Answers1

0

The stored procedure when executed manually works just fine.

Try executing BEGIN TRAN; first and then running the stored procedure when you are testing it. Does it error? It should get the same error as you are seeing when executing it from the Trigger. Triggers execute within a system-initiated Transaction, so what you are describing makes sense.

Here are some things to try:

  1. Rather than connect to the DB to write anything, return the value(s) back to the calling process and then do the writing from there. This is your best-bet, if it is possible to do, as it decouples the dependency and gives you more flexibility with how / when / where to use the SQLCLR code.

  2. Use Context Connection = true; for the Connection String

  3. Specify enlist=false; in the Connection String to not use the default behavior of trying to attach to the existing Transaction.

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
  • Thank you for your comment. I have added Begin Tran; before executing the stored procedure and I still get the same error. My CLR does return the results back to calling sql stored procedure and the stored procedure then writes back to DatabaseB. – Abe Dec 01 '17 at 15:55
  • @Abe Yes, when running the test of the proc by itself that was not getting the error, you should start getting the error when using `BEGIN TRAN` as it simulates what is happening when running that proc from within a Trigger. And even if you are doing #1 already (unclear from the description in the question), then what about items 2 and 3? You haven't shown in the question exactly how you are connecting. The error message indicates an external connection that is trying to enlist in the current transaction. Doing either 2 or 3 should fix that. – Solomon Rutzky Dec 01 '17 at 17:31
  • @Abe Also, if the SQLCLR object returns the values to be written, why is it connecting to the database in the first place? To get the value to unpack? Or is that passed in and something else is being done? – Solomon Rutzky Dec 01 '17 at 17:34
  • Thanks for your comments. The CLR is written in C# and used for deserialising the data i.e., unpacking. SQL SP get the raw outputs - Calls CLR - CLR deserialises and returns deserialised data back to SQL - SQL then performs actions on the unpacked data. – Abe Dec 04 '17 at 14:57
  • @Abe Ok, thanks for that additional info. Still, you haven't answered the question regarding "why is the SQLCLR code connecting to the database / making a `SqlConnection` in the first place?" If it is just unpacking and returning one or more values, why the need to open a Connection? AND, have you tried suggestions 2 and 3 yet? – Solomon Rutzky Dec 04 '17 at 15:49