MSDTC Exception during Transaction: C#

Question

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

Answer 1

Is it possible that aspInsertUSAZipCode interacts with a linked server? If it does then it will try and promote your current local transaction to a distributed transaction (with transactional integrity preserved between your server and the linked server).

It may be necessary to do this outside of a transaction, if the MSDTC on the remote server cannot be configured for distributed transactions. I think the best way to do this is to create a temporary table and then SqlBulkCopy your records into it and then execute aspInsertUSAZipCode using the temporary table on the server. You may need to use a cursor.

The purpose of the temporary table is so that if something goes wrong, the table is removed at the termination of your connection.