3

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

0

4 Answers 4

3

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.

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"

2
  • I will need a distributed transaction here because I will be modifying tables on both the local server and the linked server. Apr 4, 2012 at 15:47
  • hmm, that's too bad. I always try to avoid DTC because it is so finicky and sometimes buggy. there are many attractive alternatives including CLR and service broker.
    – Mordechai
    Apr 4, 2012 at 19:29
1

I had the same problem and decided to change from using "4-part names" to "3-part names" when accessing local resources over a loopback linked server. I found this information in this article by Daniel Adeniji, where he says:

The reason for the failure is that “Distributed Transactions”, the underlying infrastructure that co-ordinates transaction management only works when objects referenced via 4 part names are actually remote.

0
0

In my case I was using SQL 2005 and got "transaction context in use by another session" when running Insert....exec over a linked server. The fix for me was to patch from SP2 build 3161 to SP3. SP2 cumulative 5 is supposed to fix though.

https://support.microsoft.com/en-us/kb/947486

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.