Linked servers in SQL Server provide a way to access data from another SQL Server instance or even from other database systems. However, working with linked servers can sometimes be challenging, as errors can occur frequently. In this blog post, we will discuss one such error and its solution in detail.

The OLE DB Provider Error

Let’s consider a scenario where we have a linked server named “BIGSERVER” and we want to update a value in a table called “Employee” in the “SQLAuthority” database on that server. Here is the query we are running:

BEGIN DISTRIBUTED TRANSACTION
UPDATE BIGSERVER.SQLAuthority.dbo.Employee
SET SALARY = 'Y'
WHERE ID = 5
COMMIT TRANSACTION

However, when executing this query, we encounter the following error:

OLE DB provider "SQLNCLI11" for linked server "BIGSERVER" returned message "The transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "BIGSERVER" was unable to begin a distributed transaction.

The error message indicates an issue with the setting of the Distributed Transaction Coordinator (DTC). To resolve this, we need to make some changes in the DTC settings. Here are the steps:

1. Go to Start > Run > dcomcnfg.
2. Open the Component Services interface.
3. Navigate to the Security tab and open the Properties.
4. Change the settings as shown in the screenshot.
5. Restart the DTC Service.

After making these changes, we encounter a new error:

OLE DB provider "SQLNCLI11" for linked server "BIGSERVER" returned message "Cannot start more transactions on this session.".
Msg 7395, Level 16, State 2, Line 3
Unable to start a nested transaction for OLE DB provider "SQLNCLI11" for linked server "BIGSERVER". A nested transaction was required because the XACT_ABORT option was set to OFF.

This error indicates that we need to enable the XACT_ABORT option in the transaction. Here is the modified version of the query:

SET XACT_ABORT ON
GO
BEGIN DISTRIBUTED TRANSACTION
UPDATE BIGSERVER.SQLAUTHORITY.DBO.EMPLOYEE
SET SALARY = 'Y'
WHERE ID = 5
COMMIT TRANSACTION

By enabling the XACT_ABORT option, we are able to successfully update the value on the remote server.

Linked servers can be a powerful tool for accessing data from multiple sources within SQL Server. However, it is important to be aware of the potential errors that can occur and their solutions. If you have encountered any errors while using linked servers, feel free to share them in the comments section below.