Microsoft KB Archive/941152

Knowledge Base

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

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 "Steps to reproduce the behavior" section describes.

Method 1

Use the OPENROWSET function instead of a linked server.

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

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

1. 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 Local_Login represents the login ID of the user on the ComputerA\InstanceA instance of SQL Server 2005. Remote_Login 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.

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

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

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

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.

STATUS

This behavior is by design.

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.

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

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

3. 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.
4. 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.

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

6. On the ComputerA\InstanceA instance, run the following statement:

   USE DatabaseA
   GO
   
   INSERT INTO TestTable
   EXEC LNK_ServerB.DatabaseB.dbo.InsertA
   GO

REFERENCES

For more information about the OPENROWSET function, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn2.microsoft.com/en-us/library/ms190312.aspx

For more information about the OPENRQUERY function, visit the following MSDN Web site:

http://msdn2.microsoft.com/en-us/library/ms188427.aspx

For more information about loopback linked servers, visit the following MSDN Web site:

http://msdn2.microsoft.com/en-us/library/aa213286(SQL.80).aspx

Keywords: kbexpertiseadvanced kbtshoot kbprb KB941152

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.