Dan H&#228&#59;ndevik

Hi,
I want to connect to an oracle database using the msdaora provider (or any oledb provider) and use the TransactionScope to Begin/end transactions. Is this possible

When I use the code below all I get is an exception

System.Runtime.InteropServices.COMException : The Transaction Manager is not available. (Exception from HRESULT: 0x8004D01B)
at System.Data.Common.NativeMethods.ITransactionJoin.JoinTransaction(Object punkTransactionCoord, Int32 isoLevel, Int32 isoFlags, IntPtr pOtherOptions)
at System.Data.OleDb.OleDbConnectionInternal.EnlistTransactionInternal(Transaction transaction, Boolean forcedAutomatic)
at System.Data.OleDb.OleDbConnection.Open()



using (TransactionScope s = new TransactionScope(TransactionScopeOption.Required)) {
OleDbConnection con = new OleDbConnection("Provider=MSDAORA.1;User ID=abc;Password=123;Data Source=oracle");
con.Open();
...
}

If I remove the TransactionScope (or uses TransactionScopeOption.Supress) it works like a charm...


Update:
I'm using Oracle 10.2g and I have used the msdtcora.exe program for checking the oracle configuration and it passes its tests (after I fixed the reg key entries at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI for the oracle 10 dll according to http://support.microsoft.com/default.aspx scid=kb;en-us;q193893 ).
I can use the ODAC (Oracle native .net provider) and use distributed transactions over the msdtc but not the oledb (msdaora) provider.
I'm guessing that it has something with the msdtc configuration to do.

anyone




Re: Transactions Programming Error connecting to oracle via msdaora.1 using TransactionScope

Dan Handevik

Some light in the tunnel.
I have finally managed to use the msdaora provider with a TransactionScope after I configured the oracle server MSDTC security settings and updated the registry keys on the server as well (I had only configured the client before).

So now the transaction finds it's way to the MTS.

I have one question left though. It seems that after I successfully complete a transaction and tries to create another transaction, the program freezes when I open the connection (se code below)

TransactionScopeOption scopeOption = TransactionScopeOption.Required;
TransactionOptions transactionOption = new TransactionOptions();
EnterpriseServicesInteropOption enterpriseOption = EnterpriseServicesInteropOption.Full;
using (TransactionScope s = new TransactionScope(scopeOption, transactionOption, enterpriseOption))
{
OleDbConnection con = new OleDbConnection("Provider=MSDAORA.1;User ID=abc;Password=123;Data Source=oracle");
con.Open();



Any idea on what could cause the freeze





Re: Transactions Programming Error connecting to oracle via msdaora.1 using TransactionScope

Bruce Brown

Hi Dan-

As a first step, can you define "freeze" Are any exceptions thrown If so, what are they Also, can you also show us the entire TransactionScope Particurally the parts where you Complete the scope and where you close the existing database connection

Thanks.

-Bruce





Re: Transactions Programming Error connecting to oracle via msdaora.1 using TransactionScope

Hasham

Hi As you are saying teh following:

"I have finally managed to use the msdaora provider with a TransactionScope after I configured the oracle server MSDTC security settings and updated the registry keys on the server as well (I had only configured the client before)."

Can you please post or email me the steps you performed to configure the oracle server msdtc security settings and also teh steps to update the registry keys on the server. this will be very helpful for me in resolving one such issue.

Thanks

Hasham





Re: Transactions Programming Error connecting to oracle via msdaora.1 using TransactionScope

Dan Handevik

Mainly I configured the server machine the same way as the client machine.
I used the kb article
http://support.microsoft.com/default.aspx scid=kb;en-us;q193893
193893 Information about using Oracle with Microsoft Transaction Server and COM+ components
as base (its a bit outdated when it comes to windows xp/2003 and oracle 10 but it gives some pointers...)

What I did:
1. Changed the security settings om the MSDTC.

Start the Component Services (Administrative tools - Component Services)
Expand Component Services->Computers nodes
Rightclick on My Computer and select properties
Click the MSDTC tab
Click the Security Configuration... button.
Make sure that the following checkboxes are checked.
  • Network DTC Access
  • Allow Remote Clients
  • Allow Inbound
  • Allow outbound
  • No authentication Required
  • Enable XA Transactions
2. Fixed the oracle registry entries
Make sure that the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI and its values points to valid dlls in the oracle client. By default they point to the oracle 8 client. Since the dll names changes between different versions of the oracle client you have to manually adjust these values.
For Oracle 10 the values should be as follows
OracleOciLib="oci.dll"
OracleSqlLib="orasql10.dll"
OracleXaLib="oraclient10.dll"


I'm not sure that step 2 is nesseccary if you only should use msdaora driver but without that I couldnt get the Oracle Test program to run (see the knowledgebase article mentioned above





Re: Transactions Programming Error connecting to oracle via msdaora.1 using TransactionScope

Hasham

Thanks for the detailed steps Dan

I missed you to say last time that I am using Oracle 9i. And I have already updatedteh registry entries as described in step 2 above.

I assume that the step 1 also works for Oracle 9i

Thanks

Hasham