I am trying to connect into Oracle through Power Query > From Database > From Oracle Database and then I type BI_TST, which is within my TNSNAMES (located in: C:\oracle\product\10.2.0\client_2\network\ADMIN), and has the connection below inside:
BI_TST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = srv-oracle3.mycompany.com.br)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bitst)
)
)
Also i have the variable path configured as TNS_ADMIN=C:\oracle\product\10.2.0\client_2\network\ADMIN.
When I use SQL Developer to connect, not using TNS put setting all the information manually, it works!
When I try to connect using TNS within Excel I get the error:
Message=ORA-12154: TNS:não foi possível resolver o identificador de conexão especificado
ErrorCode=-2146232008
Wich means in English: "could not resolve the connect identifier"
Could anyone please help me in this issue?
Thanks in advance!
Honestly, I've had my fill of tnsnames. I understand the idea behind it and can appreciate that, but I spend more time figuring out why it's not working than I do saving the extra keystrokes.
In my opinon, just bypass TNSnames use Oracle's ezconnect:
srv-oracle3.mycompany.com.br:1521/bitst
In place of:
BI_TST
This should work for ODBC as well. If you can't direct connect through PowerQuery's Oracle driver, try going through ODBC. While it's an additional layer you probably don't want, I doubt you'll notice a performance difference, and it may abstract the difficulties you normally have to deal with.