Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-03-22
7
Medium Priority
?
2,572 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

604 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