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?