myGeneration problem with OraOleDB.Oracle provider (?)

Question

I can't make myGeneration tool to work correctly.

This is the connection string that I use:

Provider=OraOLEDB.Oracle.1;Password=pass123;User ID=user1;Data Source=oradb1src;Persist Security Info=True

And when I click on Test connection I get:

[System.Data.OleDb.OleDbException] - ORA-12154: TNS:could not resolve service name

[oradb1src is well defined in tnsnames.ora. That is not the problem.]

But when I change the provider to MSDAORA, then connection works fine, but in the Generate window I'm missing the Tables tab on which I should be able to choose tables from my database. I've red somewhere that this is normal with MSDAORA provider, cause mygeneration uses oledb provider for meta data retrieval.

But, why do I get this insane error message when using OralOLEDB.Oracle provider??? Does anyone know?

Thank you in advance...

PS. I have drivers for oracle 9.2 and oracle 10 installed on my computer.

Answer 1

I've only ever seen that error when the source wasn't correctly configured in tnsnames.ora. Since the other provider can see the database from your machine, I would try to create a TNS-less connection string (click here for an example) to make sure that's not the problem.

Answer 2

It seems that myGeneration was using oracle 9.2 driver that I also have on my machine... When I changed SQLNET.AUTHENTICATION_SERVICES to NONE in sqlnet.ora file under oracle92 folder I managed to successfuly connect to the database...

Now if I could just find a way on how to set myGeneration to use oracle10 driver by default... Anyone has any ideas?

Answer 3

I have encountered a similar error on occasion - basically, even though Oracle (and Toad) quite happily support multiple Oracle clients on one machine, Com versions of the Data Provider (i.e. any connection method NOT using Oracle native connection) will silently only use one of your clients. I've never been able to find any documentation to confirm how the client to be used is selected, and so cannot force it. Most likely, you will find that the Oracle client you are trying to use is NOT the one being used by the provider, and that the one being used by the provider does not have a complete TNSNames file.