Microsoft KB Archive/941152

= Error message when you run a distributed query against a loopback linked server in SQL Server 2005: &quot;Transaction context in use by another session&quot; or &quot;MS DTC has cancelled the distributed transaction&quot; =

Article ID: 941152

Article Last Modified on 9/3/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Workgroup Edition
 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition

-



SYMPTOMS
When you run a distributed query against a loopback linked server in Microsoft SQL Server 2005, you receive various error messages. Usually, you receive one of the following error messages:

Error message 1

Msg 3910, Level 16, State 2, Line 1

Transaction context in use by another session.

Error message 2

Msg 1206, Level 18, State 199, Line 1

The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.



CAUSE
This behavior occurs because the transaction semantics of a linked server have been changed in SQL Server 2005. Running a distributed query against a loopback linked server is not supported in SQL Server 2005.



WORKAROUND
To work around this problem, use one of the following methods based on your situation.

Note The following methods use the scenario that the &quot;Steps to reproduce the behavior&quot; section describes.

Method 1
Use the OPENROWSET function instead of a linked server.   On the ComputerA\InstanceA instance, run the following statement: USE DatabaseB GO

CREATE PROCEDURE InsertA AS BEGIN SELECT * FROM OPENROWSET('SQLNCLI', 'server=ComputerA\InstanceA;Trusted_Connection=yes;', 'SELECT * FROM DatabaseA.dbo.TestTable') END GO   On the ComputerB\InstanceB instance, run the following statement: USE DatabaseA GO

INSERT INTO TestTable SELECT * FROM OPENROWSET('SQLNCLI', 'server= ComputerB\InstanceB;Trusted_Connection=yes;', 'EXEC DatabaseB.dbo.InsertA') GO 

Method 2
Use the OPENQUERY function.

On the ComputerA\InstanceA instance, run the following statement: USE DatabaseA GO

INSERT INTO TestTable SELECT * FROM OPENQUERY(LNK_ServerB,'EXEC DatabaseB.dbo.InsertA') GO

Method 3
Define a remote server instead of a linked server.

Note The remote server functionality will be removed in the version of SQL Server that is later than SQL Server 2005.   On the ComputerB\InstanceB instance, run the following statement: sp_addserver 'ComputerA\InstanceA' GO

sp_serveroption 'ComputerA\InstanceA', 'Data Access', 'TRUE' GO

sp_addremotelogin 'ComputerA\InstanceA', 'Local_Login', 'Remote_Login' GO

USE DatabaseB GO

CREATE PROCEDURE InsertA AS BEGIN SELECT * FROM [ComputerA\InstanceA].DatabaseA.dbo.TestTable END GO Note  represents the login ID of the user on the ComputerA\InstanceA instance of SQL Server 2005. represents the login ID of the user on the ComputerB\InstanceB instance of SQL Server 2005.

Note The transaction semantics of the linked server definition differ from the transaction semantics of the remote server definition.   On the ComputerA\InstanceA instance, run the following statement: USE DatabaseA GO

sp_addserver 'ComputerB\InstanceB' GO

sp_serveroption 'ComputerB\InstanceB', 'Data Access', 'TRUE' GO

INSERT INTO TestTable EXEC [ComputerB\InstanceB].DatabaseB.dbo.InsertA GO 

Method 4
Avoid the loopback of data from a linked server. For example, you do not have to use a loopback linked server to obtain the result. Instead, you can directly obtain the result from a table. You may have to update other statements accordingly.   On the ComputerB\InstanceB instance, run the following statement: USE DatabaseB GO

CREATE PROCEDURE InsertA AS BEGIN TRUNCATE TABLE TestTable INSERT INTO TestTable SELECT * FROM LNK_ServerA.DatabaseA.dbo.TestTable END GO </li>  On the ComputerA\InstanceA instance, run the following statement: USE DatabaseA GO

EXEC LNK_ServerB.DatabaseB.dbo.InsertA GO

INSERT INTO TestTable SELECT * FROM LNK_ServerB.DatabaseB.dbo.TestTable GO </li></ol>

When you run the EXEC LNK_ServerB.DatabaseB.dbo.InsertA statement, you run the InsertA stored procedure on the ComputerB/InstanceB instance. The InsertA stored procedure inserts data into a local table. Then you obtain the data from the ComputerB/InstanceB instance, and then you insert the data into the table on the ComputerA/InstanceA instance. The statement in step 2 of method 3 causes no loopback of data from a linked server.

<div class="status_section">

STATUS
This behavior is by design.

<div class="moreinformation_section">

MORE INFORMATION
You may not encounter this behavior in Microsoft SQL Server 2000. However, a distributed query against a loopback linked server may cause the data to become inconsistent.

For example, you have a transaction on the ComputerA\InstanceA instance. In the transaction, you update some data in the TestTable table. Then, you run a distributed query that executes a stored procedure on the ComputerA\InstanceA instance. The stored procedure reads data from the TestTable table on the ComputerA\InstanceA instance. However, you have not committed the transaction. Therefore, the data that the stored procedure reads may be incorrect. Because of this problem, the transaction semantics of the linked server definition have been changed in SQL Server 2005.

Steps to reproduce the behavior
Assume that you have two instances of SQL Server 2005 on two servers. The name of one instance of SQL Server 2005 is ComputerA\InstanceA. The name of the other instance of SQL Server 2005 is ComputerB\InstanceB.   On the ComputerA\InstanceA instance, run the following statement to create a database and a table: CREATE DATABASE DatabaseA GO

USE DatabaseA GO

CREATE TABLE TestTable(Col1 int, Col2 varchar(50)) GO

INSERT INTO TestTable VALUES (1, 'Hello World') GO </li>  On the ComputerB\InstanceB instance, run the following statement to create a database and a table: CREATE DATABASE DatabaseB GO

USE DatabaseB GO

CREATE TABLE TestTable (Col1 int, Col2 varchar(50)) GO </li> On the ComputerA\InstanceA instance, create a linked server that links to the ComputerB\InstanceB instance. Assume that the name of the linked server is LNK_ServerB.</li> On the ComputerB\InstanceB instance, create a linked server that links to the ComputerA\InstanceA instance. Assume that the name of the linked server is LNK_ServerA.</li>  On the ComputerB\InstanceB instance, run the following statement: USE DatabaseB GO

CREATE PROCEDURE InsertA AS   BEGIN SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable END GO </li>  On the ComputerA\InstanceA instance, run the following statement: USE DatabaseA GO

INSERT INTO TestTable EXEC LNK_ServerB.DatabaseB.dbo.InsertA GO </li></ol>

<div class="references_section">