ADO.NET Transaction does not enlist commands after first connection is opened and closed

Question

I'm having trouble with using an Oracle Global Temporary Table on one of our servers. Here's the details of what I'm trying to accomplish.

1. Create OracleConnection.
2. Begin an OracleTransaction from that connection.
3. Create an OracleCommand to insert a single value into an Oracle global temporary table (GTT). e.g. insert into MY_GTT (SOME_FIELD) values (123);
4. Enlist above command in transaction and ExecuteNonQuery.
5. Create another OracleCommand to read that value from the Oracle GTT. e.g. select SOME_FIELD from MY_GTT;
6. Enlist above command in transaction and ExecuteReader.
7. Commit the transaction.

This case works on all of our servers except one. What is even stranger, is that this test case does work on the server in question after the worker processes are recycled, but subsequent requests do not work.

So, maybe the server is not properly recycling the connections or something like that...

Answer 1

I wasn't able to track down why the transaction was being maintained on one server but not on the other.

However, when I used the Oracle ODP.NET driver, everything worked. The driver that was not working was Microsoft's deprecated System.Data.OracleClient driver.