Transaction context in use by another session
linked-server, sql-server-2008-r2, t-sql, triggers
Solution
Loopback linked servers can't be used in a distributed transaction if MARS is enabled.
Loopback linked servers cannot be used in a distributed transaction. Trying a distributed query against a loopback linked server from within a distributed transaction causes an error, such as error 3910: "[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by another session." This restriction does not apply when an INSERT...EXECUTE statement, issued by a connection that does not have multiple active result sets (MARS) enabled, executes against a loopback linked server. Note that the restriction still applies when MARS is enabled on a connection.
http://msdn.microsoft.com/en-us/library/ms188716(SQL.105).aspx
Problem
I have a table called `MyTable` on which I have defined a trigger, like so: ``` CREATE TRIGGER dbo.trg_Ins_MyTable ON dbo.MyTable FOR INSERT AS BEGIN SET NOCOUNT ON; insert SomeLinkedSrv.Catalog.dbo.OtherTable (MyTableId, IsProcessing, ModifiedOn) values (-1, 0, GETUTCDATE()) END GO ``` Whenever I try to insert a row in `MyTable`, I get this error message: Msg 3910, Level 16, State 2, Line 1 Transaction context in use by another session. I have `SomeLinkedSrv` properly defined as a linked server (for example, `select * from SomeLinkedSrv.Catalog.dbo.OtherTable` works just fine). How can I avoid the error and successfully insert record+execute the trigger?