Linked servers: Transaction context in use by another session

Question

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%.

Answer 1

what are you trying to do? for most simple data retrievals from linked servers you don't need to start a distributed tran. it shouldn't effect your ability to roolback a tran if that's what you're worried about. so just say "BEGIN TRANSACTION"