All Forums Connectivity
adam.mcdonnell 5 posts Joined 02/13
13 Mar 2013
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!
 

NetFx 346 posts Joined 09/06
14 Mar 2013

No; Teradata Database does not support distributed transactions.

adam.mcdonnell 5 posts Joined 02/13
14 Mar 2013

Cool, thanks for the response :-)

mmccurrey 4 posts Joined 04/10
19 Sep 2014

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

You must sign in to leave a comment.