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