Can operations to a Teradata DB made via the .NET Data Provider for Teradata participate in a distributed transaction?

Connectivity

Can operations to a Teradata DB made via the .NET Data Provider for Teradata participate in a distributed transaction?

Hello. I'm working with some .NET code at the moment that performs the following operations:

  1. Takes data from the Teradata database
  2. Transforms the data
  3. Dumps the transformed data into our own SQL Server database
  4. Updates the relevant rows in the Teradata database

I'm trying to wrap the whole operation in a single transaction (using the .NET framework's TransactionScope object), but the updates to the Teradata data always get committed, even if I throw an exception before calling TransactionScope.Complete(). I believe my efforts here are being thwarted by Teradata's "auto-commit" feature. 

I get the desired results with multiple Transaction objects, like so:


           //SQLServer and Teradata are Entity Framework ObjectContext objects

            using (var SQLServerTransaction = SQLServer.Connection.BeginTransaction())

            {

                using (var TeradataTransaction = Teradata.Connection.BeginTransaction())

                {

                    var Data = GetDataFromTeradata();

                    InsertDataIntoSQLServer(Data);

                    UpdateTeradata(Data);

                   throw new TestException("None of the above changes get committed to the datastores.");

                   SQLServerTransaction.Commit();

                   TeradataTransaction.Commit();

                }

            }

But using a single TransactionScope to handle both transactions might be nice. Am I correct in thinking that the auto-commit feature is the problem here, and if so can I turn it off? Or is there another way to do what I'm trying to achieve? Or should I just stick with the seperate Transaction objects?

Thanks!
3 REPLIES
Teradata Employee

Re: Can operations to a Teradata DB made via the .NET Data Provider for Teradata participate in a distributed transaction?

No; Teradata Database does not support distributed transactions.

Re: Can operations to a Teradata DB made via the .NET Data Provider for Teradata participate in a distributed transaction?

Cool, thanks for the response :-)

Re: Can operations to a Teradata DB made via the .NET Data Provider for Teradata participate in a distributed transaction?

I hate to disagree, but we have distributed transactions between Teradata and MSSQL working just fine.