?
Solved

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

Posted on 2011-03-22
7
Medium Priority
?
2,474 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
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit 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

Industry Leaders: 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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

765 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