How to query Oracle from SQL Server?

Question

I'm having difficulties in creating a connection from my SQL Server 2008 Enterprise SP2 x64 into the Oracle database 10g even though I have already install the Oracle Client 11g R2 ?

I've followed this article from steps URL: http://www.ideaexcursion.com/2009/01/05/connecting-to-oracle-from-sql-server/

plus added: TNS_ADMIN into the Server variables which point into: C:\Oracle\product\11.2.0\client_1\network\admin

what is working now:

1. TNSNAMES.ORA has been copied successfully from the other Developer wworkstation
2. i can TNSPING into the DB instance
3. i can connect to the database using SQLplus and perform any SQL commands
4. i can create the DSN ONLY when using "[b]C:\Windows\SysWOW64\odbcad32.exe[/b]" the normal odbcad32 doesn't show my DSN that I have just created ?
5. the DSN created from the above works fine from the test connection.

my goal: To be able to select the Oracle connection in the Linked server object but still no effect after I restart the server. (Windows Server 2008 Enterprise 64 bit SP2).

Any idea please in resolving this problem would be greatly appreciated.

Thanks.

Answer 1

OK, the answer is that make sure the server Oracle listener is correct and working. I just found out from the DBA that there were Oracle service maintenance last month that I didn't know.