Solved

Getting ORA-12154 error with Oracle data connection in Excel 2007

Posted on 2011-03-22
7
2,402 Views
Last Modified: 2012-05-11
I'm attempting to set up an Oracle database connection in Excel 2007. To do this, I'm selecting "From Other Sources" from the "Data" menu. I then select "From Data Connection Wizard". In the wizard, I select "Other/Advanced". The Data Link Properties dialog opens. I select "Oracle Provider for OLE DB" as the Provider. On the "Connection" tab, I enter the name of the database server into the "Data Source" field, the User Name and the Password. I then test the connection. I was able to get this working once, and I then saved the connection as an ".odc" file. However, since that one time of getting it to work, I have been unable to get it working again, even though I'm entering the exact same connection information. I consistently get this error:

"Test connection failed because of an error in initializing provider. ORA-12154: TNS:could not resolve the connect identifier specified"

I get the same behavior if I select "Microsoft OLE DB Provider for Oracle" as the provider. What gives? Is there some setting in Excel that I need to set to get this to work? It worked once, so it should work again, right?

On a related issue, when I got it working the first time, it gave me a list of tables from the database to which I could connect as the data source. However, I want to pull data from a VIEW, not a TABLE. I thought I could do this through VBA code, but I couldn't get the Oracle connection to work there, either.
0
Comment
Question by:DanielBorson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 7

Expert Comment

by:Jignesh Thar
ID: 35192246
It is possible that Oracle schema that you configured is no more available from your machine. Can you Tnsping the oracle data source?

I would suggest you to check host entry in tnsnames.ora file and ensure that it is configured properly.
0
 

Author Comment

by:DanielBorson
ID: 35192416
I can connect through all my other apps (SQL Developer, SQL Plus, etc.), and I can successfully "tnsping" that database server. I've just updated all my tnsnames.ora files, and they're all configured properly. Why could I connect to the database, and then an hour later it doesn't work? And in that hour, I've changed no tnsnames.ora files, or anything else?
0
 
LVL 7

Expert Comment

by:Jignesh Thar
ID: 35192514
DanielBorson - Ok in that case , I am sure what is causing this. Does connection work from access?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:DanielBorson
ID: 35192823
I'm trying to connect through Access, and here's the behavior I'm seeing:

I select "Get External Data - ODBC Database".
I'm attempting to set up a "Machine Data Source". If I select as a driver "Microsoft ODBC for Oracle", I get the same 12154 error.
If I use the "Oracle in OraClient10g_home1" driver, I do not see any of my tnsnames entries in the dropdown for "TNS Service Names" in the Oracle ODBC Driver Configuration dialog, and entering the TNS Service name results in the same 12154 error.
If I use the "Oracle in OraHome92" driver, I DO see all my servers from the tnsnames in the dropdown list, but I still get the 12154 errror when attempting to test the connection.

It's very difficult to tell just which tnsnames.ora file is being used for what, but they've all been updated, so it should connect. It also doesn't make a difference which server I try to use, it never works.
0
 

Author Comment

by:DanielBorson
ID: 35201794
Is anyone able to give me a clue as to what's wrong or how to troubleshoot this? Does anyone know specifically which Oracle driver I should use? Does anyone know which "tnsnames.ora" file Excel 2007 uses? Does that depend on the driver? Why are there more than one Oracle driver available in Excel? What's the difference? Finally, how do I get this work?
0
 

Accepted Solution

by:
DanielBorson earned 0 total points
ID: 35207899
I was able to get this working by adding a "LISTENER" line to the tnsnames.ora file. The line looks like this:

LISTENER_DSN = (ADDRESS=(PROTOCOL = TCP)(Host = servername)(Port = 12345))

where "DSN" is the service name of the database in the tnsnames.ora file. Once I did this, I could connect from Excel or Access through an ODBC connection or from Excel through an OLE DB for Oracle connection. There needs to be one LISTENER line for each service name for which you want to do this.

0
 

Author Closing Comment

by:DanielBorson
ID: 35239078
No one provided me with a solution, and I figured it out on my own, but thought others could benefit from my experience.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question