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

MORE INFORMATION

You may receive error 7391 while working with linked servers.

In Microsoft SQL Server 2005, you receive the following error message:
In SQL Server 2000, the error message you receive is:
In SQL Server 7.0, the error message you receive is:
In some instances, you may also receive the following error 8522:


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