MSDTC Exception during Transaction: C#

I am getting a MSDTC exception in a Transaction in a C# application. The functionality is to upload one lakh (one hundred thousand) zipcode records into database tables after reading from a csv file. This operation is done in around 20 batch database operations (each batch containing 5000 records). The functionality is working fine, if I don’t use transaction.

The interesting part is that other functionalities that uses transactions are able to complete their transactions. This leads me to an assumption that the exception message is a misleading one.

Any thoughts on what could be the issue?

Exception: “Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool.”

Source: System.Transactions

Inner Exception: “The transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D024)”

Note: There is a for loop inside the transaction. Is it causing any issue?

The actual requirement is: There are some existing zipcodes in zipcode table. Each month the administrator will upload the new zipcode csv file. The new items from csv get inserted. Zipcodes which are not available in csv (but present in database) are considered to be retired and is to be deleted. The list of retired zip codes is to be returned to the User Interface. The newly added zip codes also need to be returned.

    private void ProcessZipCodes(StringBuilder dataStringToProcess, int UserID)
    {
        int CountOfUnchangedZipCode = 0;
        string strRetiredZipCode = "";
        string strNewZipCode = "";
        dataStringToProcess.Remove(dataStringToProcess.Length - 1, 1);

        if (dataStringToProcess.Length > 0)
        {

            List<string> batchDataStringList = GetDataStringInBatches(dataStringToProcess);

           //TimeSpan.FromMinutes(0) - to make transaction scope as infinite.
            using (TransactionScope transaction = TransactionScopeFactory.GetTransactionScope(TimeSpan.FromMinutes(0)))
            {

                foreach (string dataString in batchDataStringList)
                {
                    PerformDatabaseOperation(dataString, UserID);
                }

                transaction.Complete();
            }
        }


    }

    private List<string> GetDataStringInBatches(StringBuilder dataStringToProcess)
    {

        List<string> batchDataStringList = new List<string>();
        int loopCounter = 0;
        string currentBatchString = string.Empty;
        int numberOfRecordsinBacth = 5000;
        int sizeOfTheBatch = 0;

        List<string> individualEntriesList = new List<string>();
        string dataString = string.Empty;
        if (dataStringToProcess != null)
        {
            dataString = dataStringToProcess.ToString();
        }
        individualEntriesList.AddRange(dataString.Split(new char[] { '|' }));


        for (loopCounter = 0; loopCounter < individualEntriesList.Count; loopCounter++)
        {

            if (String.IsNullOrEmpty(currentBatchString))
            {
                currentBatchString = System.Convert.ToString(individualEntriesList[loopCounter]);
            }
            else
            {
                currentBatchString = currentBatchString+"|"+System.Convert.ToString(individualEntriesList[loopCounter]);
            }

            sizeOfTheBatch = sizeOfTheBatch + 1;
            if (sizeOfTheBatch == numberOfRecordsinBacth)
            {
                batchDataStringList.Add(currentBatchString);
                sizeOfTheBatch = 0;
                currentBatchString = String.Empty;
            }


        }

        return batchDataStringList;


    }

    private void PerformDatabaseOperation(string dataStringToProcess, int UserID)
    {
        SqlConnection mySqlConnection = new SqlConnection("data source=myServer;initial catalog=myDB; Integrated Security=SSPI; Connection Timeout=0");
        SqlCommand mySqlCommand = new SqlCommand("aspInsertUSAZipCode", mySqlConnection);
        mySqlCommand.CommandType = CommandType.StoredProcedure;
        mySqlCommand.Parameters.Add("@DataRows", dataStringToProcess.ToString());
        mySqlCommand.Parameters.Add("@currDate", DateTime.Now);
        mySqlCommand.Parameters.Add("@userID", UserID);
        mySqlCommand.Parameters.Add("@CountOfUnchangedZipCode", 1000);
        mySqlCommand.CommandTimeout = 0;
        mySqlConnection.Open();
        int numberOfRows = mySqlCommand.ExecuteNonQuery();
    }

Dev Env: Visual Studion 2005

Framework: .Net 3.0

DB: SQL Server 2005

When I run the query SELECT [Size],Max_Size,Data_Space_Id,[File_Id],Type_Desc,[Name] FROM MyDB.sys.database_files WHERE data_space_id = 0 --It says the size (of log) is 128

UPDATE We have three different databases used in our application. One for data, one for history and one for logging. When I put enlist = false in the above connectionstring, for the time being, it is working. But it is in my development environment. I am skeptic about whether it will work in production also. Any thought on potential risks?

Thanks

Lijo

When you are opening more than one connection within a TransactionScope, the running transaction will automatically be escalated to a distributed transaction. For distributed transactions to work, the MSDTC on both SQL Server and the machine running the application must be configured to allow network access. SQL Server and the local DTC communicate when running distributed transactions.

The problem in your case is most likely that MSDTC on the machine running your application does not allow network access because this is the default for workstations. To fix this do the following:

1. Go to "Control Panel" -> "Aministration" -> "Component Services".
2. Browse through the tree until you get to a node called "Local DTC" or something like that.
3. Right-click and choose "Properties".
4. Go to "Security" and make sure that you allow network access and also allow inbound and outbound communication with DTC.
5. Click "Ok".

You will probably be prompted to restart DTC. There seems to be a bug in the UI because even though you accept a restart of the DTC, it will not be restarted. Instead you have to restart the DTC service manually in the service manager.

BTW, remember to close the connection after use in PerformDatabaseOperation. It is good practice to put it in a using block:

using (SqlConnection mySqlConnection = new .....)
{
    // Some code here...
    mySqlConnection.Open();
    // Some more code ...
}