Sql-server – Linked server was unable to begin a distributed transaction when query is inside a TransactionScope

linked-serversql server

I have a query that involves a Linked server. The query runs fine in SSMS and when wrapped around in C# using Dapper

 var conn = GetOpenConnection(connInitializer);
 return query(conn);

produces expected result. However, when I put the code inside a TransactionScope,

using (var trans = new TransactionScope())
{
     var conn = GetOpenConnection(connInitializer);
     var returnValue = query(conn);
     trans.Complete();
     return returnValue;
}

I got an Error:

"The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server xxx was unable to begin a distributed transaction."

Should I modify some SQL Sever configuration to make this work?

Best Answer

Since you're executing the query inside an explicit transaction, you are requiring that the query follows these ACID properties. You'll need to configure Microsoft Distributed Transaction Coordinator (MSDTC) to manage the transaction across data sources.

Since query worked originally outside the scope of a transaction, I'm guessing it's a simple select. If that's the case, you may want to consider opening separate connections for each data source, loading the required data in application objects, and merging from there to produce results.

Otherwise, be prepared to troubleshoot MSDTC connectivity issues and to monitor the transaction queue for orphaned transactions.

Related Topic