Solved

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

Posted on 2011-03-22
7
2,320 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
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

831 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