This post has been republished via RSS; it originally appeared at: SQL Server Support articles.
Recently we received such kind of issue that, when you simply just open a connection to SQL Server, you can see there was DTC transactions enlisted.
However, this only happened for the SQLOLEDB drivers, the SQL Native Client doesn't have same behavior.
The minimum reproduce code :
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;
using static System.Console;
using System.Data.SqlClient;
namespace DTCTest
{
class Program
{
const string connStrGoodOleDB = "Provider=SQLOLEDB;DataTypeCompatibility=80;SERVER=MYTESTLAB;UID=testlogin;" +
"PWD=Password01!;APP=SQLOLEDBAPP;WSID=;DATABASE=TEST";
private static object writer;
public static DbProviderFactory GetOleDbFactory()
{
return System.Data.OleDb.OleDbFactory.Instance;
}
static void Main(string[] args)
{
using (TransactionScope scope = new TransactionScope())
{
using (DbConnection connection = GetOleDbFactory().CreateConnection())
{
connection.ConnectionString = connStrGoodOleDB;
connection.Open();
scope.Complete();
}
}
WriteLine("Complete");
ReadKey();
return;
}
}
}
As you can see the above code only opened a connection, but in SQL Server profile trace, it showed I have DTCTransaction for SQLOLEDBAPP
Then I collected process monitor, and checked the stack of the whole process, found it was due to the System.Data.Oledb.OledbConnections, in the Open() function, it will check several condition, if meet, then it will automatically Enlist the transaction, and finally went to the DTC transactions related call.
And one of the conditions check was OLEDB Services, so I searched for it and found we already had a document noted this issue:
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/distributed-transactions
"You can disable auto-enlistment in existing transactions by specifying Enlist=false as a connection string parameter for a SqlConnection.ConnectionString, or OLE DB Services=-7 as a connection string parameter for an OleDbConnection.ConnectionString. "
This is also notified in below doc
You may not notice this connection string parameter, since it was not noted in the connection string part. So the solution is easy, just change my connection string as below you will get it resolved
const string connStrGoodOleDB = "Provider=SQLOLEDB;DataTypeCompatibility=80;SERVER=MYTESTLAB;UID=testlogin;OLE DB Services=-7;" +
"PWD=Password01!;APP=SQLOLEDBAPP;WSID=;DATABASE=TEST";