Why my transaction promoted to DTC transaction?

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

未命名图片.png

 

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

https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration-data-access-transactions/transaction-promotion?view=sql-server-ver15

 

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";

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.