Sql-server – Linked servers: Transaction context in use by another session

linked-serversql-server-2008-r2

I'm using linked servers to perform some queries in a distributed transaction. It doesn't work in a distributed transaction. I get the following error:

Transaction context in use by another session.

Not even this simplified example will work:

IF EXISTS(SELECT server_id from sys.servers WHERE name = 'SomeServer')
BEGIN
       EXEC sp_dropserver  @server = N'SomeServer';
END

EXEC sp_addlinkedserver 
   @server = N'SomeServer',
   @srvproduct = N'',
   @provider = N'SQLNCLI',
   @datasrc = N'localhost';

EXEC sp_serveroption
       @server = N'SomeServer', 
       @optname = 'rpc out',
       @optvalue = 'on';
GO


BEGIN DISTRIBUTED TRAN;

EXEC('SELECT TOP 100 * FROM [SomeServer].[SomeDatabase].dbo.tblFoo')

ROLLBACK;

EXEC sp_dropserver  @server = N'SomeServer';

Removing the work distributed, or removing the begin/rollback tran entirely also works. Running sp_whoisactive returns no rows. I have had to restart the server (on my laptop) several times because there were distributed transactions I was unable to kill even if I killed all the spids returned by sp_whoisactive.

In those possible related instances, killing thos spids more then once gives me:

SPID X: transaction rollback in progress. Estimated rollback completion: 0%.

Best Answer

http://msdn.microsoft.com/en-us/library/ms188716.aspx

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.

Related Question