1 Reply Latest reply: Dec 20, 2012 1:55 PM by willjamu RSS

    Transaction Scope and Oracle and SQL Server

    willjamu
      I was thinking the below fix post 11.2.0.3 was the solution to my issue.

      Oracle 11.2.0.3 client with the be patch against an Oracle 11.2.0.2 database and a SQL 2012 instance.

      *12352406 - PROBLEM IN COMMITTING A TRANSACTION IS MSDTC WHEN SQL CONNECTION IS OPENED FIRST*

      Basically, I have cobbled togther some code from different sources to perform a POC using MSDTC to backout transactions from two different resources managers (Oracle and SQL Server)

      In the below code if SQL is first it works as expected and if Oracle is first it works as expected.


      However, I can't get both resource managers to properly participate in a transaction.


      using System;

      using System.Collections.Generic;

      using System.Linq;

      using System.Text;

      using System.Data;

      using Oracle.DataAccess.Client;

      using Oracle.DataAccess.Types;

      using System.Data.SqlClient;

      using System.Transactions;



      namespace tran_sproc3

      {

      class Program

      {

      static void Main(string[] args)

      {



      string _connstring = "Data Source=TNS1,User Id=scott;Password=tiger;";

      try

      {

      OracleConnection connObj = new OracleConnection(connstring);

      // OracleTransaction _tranObj;

      //_connObj.Open();

      //_tranObj = _connObj.BeginTransaction();

      OracleCommand cmdObj = connObj.CreateCommand();





      SqlConnection cn = new SqlConnection("server=(local)\\SQL2012;database=DB1;Integrated Security=SSPI;");

      SqlCommand cmd = cn.CreateCommand();



      try

      {



      //using (TransactionScope scope = new TransactionScope())

      using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))

      {



      //* SQL Server

      cn.Open();

      cmd.CommandText = "pTest";

      cmd.CommandType = CommandType.StoredProcedure;

      cmd.ExecuteNonQuery();

      Console.WriteLine("SQL Server Stored proc run successfully");



      //* oracle

      _connObj.Open();

      cmdObj.CommandText = "procInsertSamplePODetails";

      _cmdObj.CommandType = CommandType.StoredProcedure;

      _cmdObj.Parameters.Clear();

      _cmdObj.ExecuteNonQuery();

      Console.WriteLine("Oracle Stored proc run successfully");



      //Intentionally cause an exception

      /*

      _cmdObj.CommandText = "INSERT INTO NonExistentTable(InvID, InvDate, Remarks) VALUES(:InvID, SYSDATE, :Remarks)";

      _cmdObj.CommandType = CommandType.Text;

      _cmdObj.Parameters.Clear();

      _cmdObj.Parameters.Add(new OracleParameter("InvID", "A02"));

      _cmdObj.Parameters.Add(new OracleParameter("Remarks", "Sample invoice 2"));

      _cmdObj.ExecuteNonQuery();

      //_tranObj.Commit(); */



      scope.Complete();



      }



      }

      catch (Exception ex)

      {

      Console.WriteLine("Uh oh, rollback initiated...");

      Console.WriteLine(ex.ToString());

      // _tranObj.Rollback();

      // }



      }

      finally

      {

      //_tranObj.Commit();

      _connObj.Close();

      _connObj.Dispose();

      _connObj = null;

      cn.Close();

      cn.Dispose();

      cn = null;

      Console.WriteLine();

      Console.WriteLine("ENTER to continue...");

      Console.ReadLine();

      }



      }

      catch (Exception ex)

      {

      Console.WriteLine(ex.ToString());

      }

      }

      }

      }
        • 1. Re: Transaction Scope and Oracle and SQL Server
          willjamu
          Follow these steps only if you are using ODP.NET:
          1.
          Open a command prompt. (Open via "Run As Administrator" if applying on Windows 7 or 2008R2 and User Account Control is enabled).

          2.
          Navigate to the %ORACLE_HOME%\ODP.NET\bin\2.x directory.

          3.
          Execute the following to install the new ODP.NET assembly:
          oraprovcfg.exe /action:gac /providerpath:Oracle.DataAccess.dll
          Basically I neglected to reload the GAC after installing the patch.

          The below code now works as expected


          3.3.6 ODP.NET 4.0 Installation Instructions

          Follow these steps only if you are using ODP.NET:
          1.
          Open a command prompt. (Open via "Run As Administrator" if applying on Windows 7 or 2008R2 and User Account Control is enabled).

          2.
          Navigate to the %ORACLE_HOME%\ODP.NET\bin\4 directory.

          3.
          Execute the following to install the new ODP.NET assembly:
          oraprovcfg.exe /action:gac /providerpath:Oracle.DataAccess.dll