3 Replies Latest reply: Jun 7, 2012 10:24 PM by gdarling - oracle RSS

    How to get "Oracle Services for MTS" working?

    605939
      I've implemented the microsoft code example (see at the bottom) for the use of distributed transactions (with system.transactions and MTS support). The code works fine as long as I use only MS sqlserver.

      Well, I tried one connection to an oracle database and one connection to the sqlserver - and here the trouble starts ;-)

      Oracle doesn't care about the transaction. After the command is executed the changes are made to the oracle database at once (no commit has occured at that point) even if the second command isn't successfull.

      I suppose at the moment that the problem must be something around the interaction between MTS and oracle database, more precisly the oracle Services for MTS.

      I've installed the odp.net client (ODAC1020221.exe).
      The installer tells me that oracle data access components 10.2.0.2.21 and within the Oracle Services for MTS are installed.
      But I cannot find a service with that name in the service list, OracleMTSRecoveryService is listed, but I'm not sure if that's the right one.

      So here are my questions:

      What's the name for 'Oracle Services for MTS' in the services list?
      Do I have to install more than the odp.net client to get the MTS support running?
      Is there a mistake in the code I'm running?


      any help is welcome ...
      thanx in advance!!!


      And here's the code I'm working with:

      [Transaction(TransactionOption.RequiresNew)]
      public class Worker : ServicedComponent
      {
      [AutoComplete]
      public void Test()
      {
      using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew, new TimeSpan(0, 5, 0)))
      {
      DbProviderFactory theFactory = DbProviderFactories.GetFactory("Oracle.DataAccess.Client");

      using (DbConnection connection1 = theFactory.CreateConnection())
      {
      try
      {
      connection1.ConnectionString = ".........";
      connection1.Open();

      DbCommand command1 = theFactory.CreateCommand();
      command1.Connection = connection1;
      command1.CommandText = "Insert into user (name, surname,benoid) values ('hans','wurst','32315134512345')";
      object returnValue = command1.ExecuteNonQuery();

      using (SqlConnection connection2 = new SqlConnection("Data Source=localhost;Database=UserManagement;Trusted_Connection=true;"))
      {
      try
      {
      connection2.Open();

      //this command fails
      SqlCommand command2 = new SqlCommand("Insert into uuuu (name, vorname) values ('hans','wurst')", connection2);
      object retValue = command2.ExecuteNonQuery();
      }
      catch (Exception ex)
      {
      return;
      }
      }
      }
      catch (Exception ex)
      {
      return;
      }
      }

      scope.Complete();
      }
      }
      }