MTS with ORACLE - Myth or Reality ?

[pgrenette]

Hi,

I'm trying (for several weeks now) to enlist an Oracle connection in a MTS transaction.
It fails whatever I try.

Using the Microsoft ODBC driver, my SQLConnect statement returns SQL_ERROR and the associated message is always the same (who never had this f%#2ing message with MTS ?):

"Microsoft][ODBC Driver Manager] Failed to enlist on calling object's transaction"

But when I'm looking in the Event Viewer Application, my Oracle seems ready to go:

"The description for Event ID ( 0 ) in Source ( COM Contexts ) could not be found. It contains the following insertion string(s): Failed to enlist in DTC: SQL state NA000, native error 0, error message [Microsoft][ODBC driver for Oracle][Oracle]ORA-00000: normal, successful completion."

hum...

Now, if I use the Oracle ODBC driver 8.00.0500 instead the Microsoft one, I have also an error with the following message in the event viewer:

"The description for Event ID ( 0 ) in Source ( COM Contexts ) could not be found. It contains the following insertion string(s): Failed to enlist in DTC: SQL state HYC00, native error 0, error message [Oracle][ODBC]Optional feature not implemented.."

My God ! That's not what they said in the documention...


Any Help ?


Here is my configuration:

Oracle server 8.0.5 (NT server)
Oracle client 8.0.5 (NT 4 workstation + SP4.0)
MTS 2.0 + Patches
Microsoft ODBC Driver for Oracle: 2.573.292700
Oracle ODBC Driver: 8.00.0500

If anybody was able to enlist in MTS transaction with Oracle, please, tell me how !

[James Jory]

We're using Oracle 7.3 on HP-UX and haven't had any problems. However, the MTS docs on using MTS with Oracle were very useful when we setup our systems. If you haven't already, you may want to check out the docs at ftp://ftp.microsoft.com/bussys/viper.

I don't think you'll get things working with any ODBC driver other than Microsoft', though.

Good luck.

[pgrenette]

Ok but, did you use ODBC to access to your Oracle SGBD ? If so, what is your driver version (MSORCL32.DLL) ?
Do you have a sample which I could try ?

My sample is just a simple ATL object which try to do a SQLConnect on the IObjectContext::Activate method and it fails.
If you have something I could try here, I will be very interested !

Regards,
Pierre

[James Jory]

We're using the same MS ODBC for Oracle driver that you're using (2.573.292700). However, we're using ADO 1.5 (with the ODBC provider) to access the database and not ODBC directly.

Our ADO code is not doing anything special so I'm not sure a sample would help you much (i.e. open connection, perform update, close connection, commit txn...).

Have you read the "failed to enlist" document that can be found on MS' FTP server that I mentioned in an earlier post? It has some helpful information

[pgrenette]

Thanks for your help. You give me the Hope !
I'm still checking each point on the 'Failed to Enlist' doc.
But, even if your ADO code is not doing anything special, I am interested by a sample.
I never write an ADO line of code so, just a 'login/logout' sample in C (or COM-C++) will be helpfull !
I appreciate your help.
Regards,
Pierre

[James Jory]

In C++, here is some sample ADO code that will perform an update.

#import "msado15.dll" no_namespace rename("EOF", "adoEOF")

try {
// Create connection
_ConnectionPtr spConn(__uuidof(Connection));

spConn->ConnectionString =
"SERVER=???;UID=???;PWD=???";

// Open connection
spConn->Open("", "", "", -1);

// Create command and statement
_CommandPtr spCmd(__uuidof(Command));
spCmd->ActiveConnection = spConn;
spCmd->CommandText = L"UPDATE ...";

// Execute command.
spCmd->Execute(0, 0, -1);
}
catch (_com_error& e) {
// handle error
}

Of course, if this code was being used in an MTS component, you'd add a call to SetComplete after the Execute and a call to SetAbort in the catch block.

[pgrenette]

Thank's for your help but I still have the same problem: unable to enlist in transaction.

I put your code inside a simple ATL object and try it in the MTS runtime. With no transaction (ie. 'Does not support transaction' property), the Open statement runs fine. But if I change the property to 'Requieres a transaction' the Open throw an exception (E_FAIL) and I have the following message in the event viewer:

"The description for Event ID ( 0 ) in Source ( COM Contexts ) could not be found. It contains the following insertion string(s): Failed to enlist in DTC: SQL state NA000, native error 0, error message [Microsoft][ODBC driver for Oracle][Oracle]ORA-00000: normal, successful completion."

Pretty cool, hum ?

I tryed also with ATL Accessor model: same thing.

The TestOracleXAConfig.exe works great.

The Bank Sample does NOT: same error - Failed to enlist in transaction.

Any idea ?

[James Jory]

Be careful when you're debugging transactional components. If you take too long stepping through the code the transaction may timeout on you. For some of our longer running txns we sometimes get an ORA-0000 but bumping up the txn timeout eliminates the problem. When debugging you can set it to 0 to indicate no timeout limit.

Also, what version of ADO are you using. We're using 1.5.

[pgrenette]

I already set my transaction timeout 0 and I try with ADO 2.0 "C:\Program Files\Common Files\System\ado\msado15.dll" version 2.0.3002.23

AND also with DAO 3.5 (using ATL accessors)
AND also ODBC 2.0, 2.5 and 3.0

If you known another flashing Microsoft technology I didn't try... ... before switching to CORBA.

Thank you for your help.

[James Jory]

We haven't gone to ADO 2.x yet but have had to battle keeping our machines at ADO 1.5. Everything we install insists on installing ADO 2.0 so we have to be very careful to back out these installs. Checking the version of msado15.dll is only the beginning. The only reliable method we have found to roll back to (or ensure we were on) ADO 1.5 is by using a utility called ClsidView. Search for it on MS' web site.

Of course, the other difference is that we're using Oracle 7.3 and not 8.