Knowledge Base

How to troubleshoot error 7391 that occurs when you use a linked server in SQL Server

Article ID: 306212

Article Last Modified on 2/16/2007

APPLIES TO

Microsoft SQL Server 2000 Standard Edition
Microsoft SQL Server 7.0 Standard Edition
Microsoft SQL Server 2005 Standard Edition
Microsoft SQL Server 2005 Developer Edition
Microsoft SQL Server 2005 Enterprise Edition
Microsoft SQL Server 2005 Express Edition
Microsoft SQL Server 2005 Workgroup Edition

This article was previously published under Q306212

SUMMARY

This article is a basic guide for troubleshooting linked server error 7391. The article contains guidelines and directions to determine the cause and resolution for this error. However, this is not a complete list, and some problems may be specific to your environment.

MORE INFORMATION

You may receive error 7391 while working with linked servers.

In Microsoft SQL Server 2005, you receive the following error message:

Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because OLE DB provider "%ls" for linked server "%ls" was unable to begin a distributed transaction.

In SQL Server 2000, the error message you receive is:

Server: Msg 7391, Level 16, State 1, <ObjectName>, Line xx
The operation could not be performed because the OLE DB provider '%ls' was unable to
begin a distributed transaction.

In SQL Server 7.0, the error message you receive is:

Server: Msg 7391, Level 16, State 1, Line 1 The operation could not be performed because
the OLE DB provider '%ls' does not support distributed transactions. [OLE/DB provider
returned message: Distributed transaction error]

In some instances, you may also receive the following error 8522:

Distributed transaction aborted by MSDTC.

All error numbers that are in the range of 7300 to 7399 indicate a problem with the provider. By default, because each provider may have different capabilities and return different details, you do not receive the full error message. To retrieve the full error message from providers, issue this command before you run the query that results in the error:

DBCC TRACEON (3604, 7300)

If you receive the error 7391 from a process such as replication or Data Transformation Services (DTS), you may also receive the error message when the code contains a BEGIN DISTRIBUTED TRAN statement.

Make sure that you test the code that has the BEGIN DISTRIBUTED TRAN statement in Query Analyzer.
Start Query Analyzer, and then run this statement by logging on to the server with the same account as the SQL Server and the SQL Server Agent startup account. This will help to narrow your troubleshooting focus.

Common Resolutions

Here is a list of the most common resolutions for error 7391.

Note It is a good idea to limit your code in a transaction that involves a distributed query only to the remote server. In most cases, you may separate locally executed steps from remote steps to reach this goal.

Note If you are using SQL Server 2005, use SQL Server Management Studio instead of Query Analyzer and Enterprise Manager to perform the operation mentioned in this article. Notice that some user interfaces in SQL Server Management Studio may differ from the user interfaces in Query Analyzer or in Enterprise Manager. See the related topic in SQL Server 2005 Books Online, and make the corresponding change to the operation.

Supportability Issues

Contact the vendor of the driver you use in your linked server query to see whether or not the driver supports distributed transactions.
Check whether the object on the destination server refers back to the first server. This is what is known as a loopback situation. This is not supported, as documented in SQL Server Books Online. For more information, visit the following Microsoft Web site:

Loopback Linked Servers

Communication Issues

Verify that your network name resolution works. Verify that the servers can communicate with one another by name, not just by IP address. Check in both directions (for example, from server A to server B and from server B to server A). You must resolve all name resolution problems on the network before you run your distributed query. This may involve updating WINS, DNS, or LMHost files. For more information, see the following article in the Microsoft Knowledge Base:

169790 How to Troubleshoot Basic TCP/IP Problems
If you have a firewall, make sure that your Remote Procedure Call (RPC) ports are opened correctly.

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

154596 HOWTO: Configure RPC Dynamic Port Allocation to Work w/ Firewall

179442 How to Configure a Firewall for Domains and Trusts

287932 INF: TCP Ports for Communication to SQL Server Through Firewall
Check the object you refer on the destination server. If it is a view or a stored procedure, or causes an execution of a trigger, check whether it implicitly references another server. If so, the third server is the source of the problem. Run the query directly on the third server. If you cannot run the query directly on the third server, the problem is not actually with the linked server query. Resolve the underlying problem first.
Check whether you are using Remote Access Server (RAS) to access remote servers. If so, make sure that you have implemented Routing RAS (RRAS). Linked server does not work on RAS because RAS allows only one way communication.

Configuration Issues

Start the Distributed Transaction Coordinator (DTC or MSDTC) on all servers that are involved in the distributed transaction.
Issue this statement before you run your query:
```
SET XACT_ABORT ON
```
' The XACT_ABORT option must be set to ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. This option is not required if the provider supports nested transactions.
Check whether any of the servers are on a cluster. The DTC on the cluster must have its own IP address. You must verify proper name resolution of the DTC service on each server. The IP address of the DTC must be defined in your name resolution system (such as WINS, DNS or LMHosts). Verify that each server can communicate with the other's MSDTC by name, not just by IP address. Check in both directions. For example, check from server A to server B's MSDTC, and then check from server B to server A's MSDTC. You must resolve all name resolution problems on the network before you run your distributed query.
If you are using earlier remote servers instead of the recommended linked servers, set the remote proc trans
configuration option setting to OFF for the server, or issue a SET REMOTE_PROC_TRANSACTIONS OFF
statement before you run any distributed query. If this setting is set to ON, the remote procedure calls
are made in a local transaction.
Check the return value of the system function @@SERVERNAME on both servers. Verify whether the
return value matches the computer name of each server. If it does not match, you have to
rename the server.
For SQL Server 2000, see the following article in the Microsoft Knowledge Base:

303774 BUG: Renaming A Server Topic in Books Online is Incomplete

For SQL Server 7.0, review question 5 in the following Microsoft Knowledge Base article:

195759 INF: FAQs - SQL Server 7.0 - SQL Setup
Verify that the SQL Server startup account has full control permissions on this registry key:

HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer

REFERENCES

For more information about configuring linked servers, visit the following Microsoft Web site:

Configuring Linked Servers

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

295086 FIX: MS DTC Limited to 16 Nodes in Single Distributed Transaction

175496 XCON: Using RPCPING To Troubleshoot MTA Connections

280106 HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in SQL Server

Keywords: kbwins kbregistry kbprovider kbdtc kbdriver kbtransaction kbrpc kbtshoot kberrmsg kbclustering kbip kbinfo KB306212

Microsoft KB Archive/306212

Contents