Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Error ORA-12514 when trying to connect. (Read 20686 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Error ORA-12514 when trying to connect.
16.02.06 at 13:25:15
 
Error ORA-12514 when trying to connect.
 
Quote:
We use the SQL*XL software to connect Excel to our databases for business intelligent queries. We upgraded one of our servers to Oracle 10g. I installed the Oracle 10g client on my laptop. I now have both the Oralce 8.0.5 & Oracle 10.g clients on my machine. SQL XL has stopped working.

When I use SQL*XL to connect from Excel to a database (either 8.05 or 10g) I get:

"Oracle Connection Error received : Unable to make conection, ORA-12514: TNS:listerner does not currently know of service requested in connect descriptor."

Why not give SQL*XL a try today.
SQL*XL: SQL Excel software

Download SQL*XL now!


Answer:
This is a TNS problem. While SQL*XL will try to use several different connections in the connection wizard to connect Excel to the Oracle database, this is a situation that it cannot overcome. The TNS configuration specifies the name under which the database is known on the computer and hides all server details. The problems must be sought in the specification of the TNS names and/or in the setup of the database server.
 
Possibly your new database has a different listener. The configuration of your TNS name / Database alias may not be correct anymore. You can fix this by making a new database alias or TNS name using the Oracle configuration utilities on your PC. You can also try to edit the tnsnames.ora file on your PC if you know what you are doing.  
 
The following command may also be of help. Do Start -> Run : TNSPING <<service name>>
With service_name I mean the name of your database, the name you defined in your tnsnames.ora as the alias for your database.
 
Further, I found the following general description for your error message:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor  
 
Cause:  
The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.  
 
Action:  
- Wait a moment and try to connect a second time.
- Use the SQL*XL connection wizard so it can try the different connection types for you.
- Check which services are currently known by the listener by executing: lsnrctl services <listener name>
- Check that the SERVICE_NAME parameter in the connect descriptor of the net service name used specifies a service known by the listener.
- If an easy connect naming connect identifier was used, check that the service name specified is a service known by the listener.
- Check for an event in the listener.log file.  
 
References:
SQL*XL: Excel to database bridge
 http://www.oraxcel.com/projects/sqlxl
Other database connection topics
 http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?board=sqlxl_connect
Connecting Excel to various database types:
 http://www.oraxcel.com/projects/sqlxl/help/techniques/connecting/index.html
Back to top
 
« Last Edit: 01.08.11 at 22:20:03 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Error ORA-12154 when trying to connect
Reply #1 - 31.03.06 at 12:29:45
 
Fernando from Colombia asked the following question.
 
Question:
I have office 2003 and a oracle 9i and 10g Databases. Actually, there are several oracle homes in my PC, but when i try to establish conection with your SQL*XL software from Excel an ORA-12154 is shown. I installed  ODAC101040 from Oracle, this is 10g version.
 
Answer:
ORA-12154: TNS:could not resolve service name
 
It means that the database could not be found. Oracle uses a set of aliases to resolve the connection details to your database. These aliases are sometimes called TNS names or TNS aliases or service names.
 
It is usually due to Oracle searching in the wrong tnsnames.ora - which is the configuration file where the TNS names are specified. If you have multiple Oracle homes you need Oracle to look in the correct directory. There may be multiple tnsnames.ora files.
 
You can always use the DOS command tnsping to try to resolve the database. It will enable you to verify the TNS name configuration without adding the complexity of drivers etc.
 
For further info I suggest you look at:  
http://www.cryer.co.uk/brian/oracle/ORA12154.htm
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Error ORA-12514 when trying to connect.
Reply #2 - 19.05.08 at 13:42:41
 
Setup new TNS name in Oracle 11g
 
From the start menu, start the Net Configuration Assistant
Select Local Net Service Name configuration
Select Add
 
Service Name: Enter new service name
Select Protocols: Select TCP
Select hostname and port number
 
The above procedure adds a new TNS name that you can use as database name in SQL*XL to connect to a database.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Error ORA-12514 when trying to connect.
Reply #3 - 13.09.08 at 11:19:02
 
Someone just reported this problem again today but sent his full error messages. Perhaps it is handy to see what error is precisely shown when you try to connect using different drivers. Please find the advice what to do about these errors in the postings earlier in this tread.
 
 
Quote:
Microsoft Data Link Error

Test connection failed because of an error in initializing provider.
ORA-12514: Message 12514 not found; product=RDBMS80; facility=ORA

 
Quote:
SQL*XL could not connect to your Oracle database. The database listener could not connect to the database.

Driver Error Message:
[Microsoft][ODBC driver for Oracle][Oracle]ORA-12514: Message 12514 not found; product RDBMS80; facility=ORA

This can be a temporary condition. Wait a moment and try again. If you keep getting this error, please inspect your listener setup or consult your DBA.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Error ORA-12514 when trying to connect.
Reply #4 - 09.01.09 at 07:37:47
 
Today I received the same error but different description:
 
ORA-12154: TNS:could not resolve the connect identifier specified
 
The problem was that I connected to a database that was not correctly configured in the tnsnames.ora.
Back to top
 
« Last Edit: 09.01.09 at 07:41:16 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
techdomi
YaBB Newbies
*


I Love SQL*XL

Posts: 0
Re: Error ORA-12514 when trying to connect.
Reply #5 - 04.06.10 at 09:23:28
 
I am a long time user of SQLXL3 on a windows 2000 machine. However, that machine recently died and I am trying to set up a new environment on a Windows 7 64-bit machine with Excel 2007.
 
I have installed Oracle 11 Instant Client and OO4O 10g. I have created a tnsnames.ora and have a TNS_ADMIN variable pointing to it. I can successfully tsnping and connect to the database using other software.
 
However, I have tried both my SQLXL3 and a new v4 demo, and when I try to connect using an OO4O connection, I get the error:
 
Unable to make connection, ORA-12154: TNS: could not resolve the connect identifier specified
 
Also in the Database Connection Wizard, none of the connections work, many of them with the above error. It appears whatever method SQLXL or OO4O is using is not finding the tnsnames.ora. Where is it looking for it and is there any way to find more info on why its not connecting?
 
Please let me know if you need any further details.
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Error ORA-12514 when trying to connect.
Reply #6 - 04.06.10 at 09:31:15
 
Are there multiple Oracle homes installed on the machine. One thing OO4O was sensitive to was the order in wich the homes appeared in the path variable. The home in which the tnsnames.ora file is located should be the first in the path.
 
This seems to be the same question:
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1140092715
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
techdomi
YaBB Newbies
*


I Love SQL*XL

Posts: 0
Re: Error ORA-12514 when trying to connect.
Reply #7 - 04.06.10 at 09:58:21
 
There are indeed two Oracle homes, because of the 11gR2 instant client, and the 10g OO4O.
They are:
Instant Client 11g: C:\Oracle\instantclient\instantclient_11_2
OO4O 10g: C:\oracle\product\10.1.0\Client\
 
My path is:
C:\oracle\product\10.1.0\Client\bin;C:\oracle\product\10.1.0\Client\jre\1.4.2\bi
n\client;C:\oracle\product\10.1.0\Client\jre\1.4.2\bin;%SystemRoot%\system32;%Sy
stemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.
0\;C:\Oracle\instantclient\instantclient_11_2;%PATH%
 
I tried moving the last entry to the first to check, but that didn't change anything. Other relevant environment variables that are set:
 
LD_LIBRARY_PATH=C:\Oracle\instantclient\instantclient_11_2
ORACLE_HOME=C:\Oracle\product\10.1.0\Client
OraClient10g_home=C:\Oracle\product\10.1.0\Client
SQLPATH=C:\Oracle\instantclient\instantclient_11_2
TNS_ADMIN=C:\Oracle\instantclient\instantclient_11_2
 
I've copied my tnsnames.ora into C:\Oracle\instantclient\instantclient_11_2 and C:\Oracle\product\10.1.0\Client\network\ADMIN just to be sure.
Back to top
 
 
  IP Logged
techdomi
YaBB Newbies
*


I Love SQL*XL

Posts: 0
Re: Error ORA-12514 when trying to connect.
Reply #8 - 04.06.10 at 10:11:51
 
In the Data Connection Wizard, the OO4O and Oracle OLE DB connection fail with the above-mentioned:
 
ORA-12154: TNS: could not resolve the connect identifier specified
 
The Microsoft OLE DB and ODBC drivers fail with one of the errors:
 
ORA-06413: Connection not open
Data source name not found and no default driver specified
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Error ORA-12514 when trying to connect.
Reply #9 - 04.06.10 at 10:30:56
 
Hi,
 
This may help you.  
 
OTN forum:
http://forums.oracle.com/forums/thread.jspa?messageID=3810917
 
Some quotes:
Quote:
the problem is in the c:\program files (x86)\ directory name. Any app that is 32 bit get stored here, and calling program information is passed to oracle, it takes the () as a special command. Blows every time. Any application including oracle clients has to be moved out of this director into some other dir, I use c:\program files x86\ and then your register has to be modified and then do a seach on all files on your machine for (x86)\xxxxx where xxxxx is the old dir name. Takes about 3 hours to fix this.

 
Quote:
The source of the problem was that the calling app was running from the \Program files (x86) directory. This somehow causes Oracle a problem. I moved the program, which is in fact a native 64-bit program to the \Program files directory and the problem went away. Hope this helps someone in the future.

 
Quote:
Moving my MS Office installation to a new folder outside of the "Program Files (x86)" folder did the trick. Thanks!

 
Question: In which directory is SQL*XL installed? In such a directory with a () in the dir name? Just move it somewhere else and re-register any dlls in that directory. Double click sqlxl.xla the first time you run it after moving the directory. You can also uninstall and re-install it in the new location. Then all the reregistering is done for you.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
techdomi
YaBB Newbies
*


I Love SQL*XL

Posts: 0
Re: Error ORA-12514 when trying to connect.
Reply #10 - 04.06.10 at 11:19:19
 
I uninstalled SQLXL4 and re-installed in C:\Oracle\SQLXL4\, but the problem still remains.
Do I need to move my MS Office installation as well?
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Error ORA-12514 when trying to connect.
Reply #11 - 04.06.10 at 11:44:57
 
I don't know. I just adviced on the basis what I read after googeling ORA-12154 and 64 bits.
 
The path I usually take when I experiment is first try the tnsping at the command prompt. That seems to use some kind of low level connection mechanism and will tell you whether the connection is possible in principle. Or stating it differently, I have not been able to connect without a positive tnsping outcome with the exception of using the instant Oracle client, which actually would be something you could try.
 
The tool for experimentation is the Microsoft Data Link Wizard. From SQL*XL 4's connection dialog you can start it. It is an environment where you can experiment with the parameters available.  
 
What also may be fruitful is to experiment with ODBC. Again from SQL*XL 4's connection dialog you can jump to the ODBC administrator page which you can also find in the administration section of the Control panel.
 
The only thing I can say is that I have had positive reports of people using the software on 64 bits machines. Perhaps someone else can post a positive message of being able to connect in such an environment?
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Investigating ORA-12514 problems
Reply #12 - 27.07.11 at 10:20:21
 
Investigating ORA-12514 problems
 
Quote:
Trying to connect with SQL-XL to Oracle I am getting:
Unable to make connection, ORA-12154: TNS could not resolve service name.

I know what that means, I was an Oracle DBA, however, the message is not true this time. I have tried to manually create the connection with SQL-XLs Connection facility (Oracle OO4O Connections), and get the same message as above.

The thing is, my TNSNAMES.ora file is just fine, and I can make that same database connection from any other tool. Its just SQL-XL. I can see mydb entries in all of the tnsnames.ora files I can find. I have 9.2 and 10.2 Oracle homes.

Any ideas what the problem is? Here is a TNSPING to that same database.

C:\>tnsping mydb

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 26-JUL-2011 07:07:44
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
C:\oracle\product\10.2.0\client_2\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL= TCP) (HOST= mypc.mydomain.com)
(PORT= 1521)) (CONNECT_DATA = (SID = mydb)))
OK (180 msec)

 
Quote:
I tried running Database Connection Wizard from the New Database Connection dialog box. The only one that worked has a Provider of MSDAORA, so I picked it Now I cant even get a connection to what I could get connected to previously. I try using OO4O, and now always get the error:

 
Thank you for your emails and screen prints. Helpful.
 
As a (former) DBA you know that the setup with Oracle homes is sometimes somewhat incomprehensible. There are many possible sources for problems, the registry, the order of directories in the path, the tnsnames.ora file the sql.ora file, correct descriptors on those, listener configs, different versions, interoperability between 32/64bits, etc, etc. Usually it is experimenting until it works. That is why SQL*XL is trying to use all the different connection types in the connection wizard.
 
I would like to see the connection wizard output again with some connections that work and others that fail like you sent me. Can you scroll to the right and make a screen print of the error messages (last column).
 
OO4O uses a different path to connect then the microsoft ADO connections (OLEDB/ODBC). I believe it is going through the OCI. That is why OO4O may behave somewhat differently. But if you have tried and tested the connection in SQL*XL and used it it should continue to work from using the connection history list. If it doesn't let me know.
 
What can't (or shouldn't) fail is when you see a successful connection in the wizard that it should always work. So, if you selected it to connect it should establish a successful connection with it and in future SQL*XL sessions when you pick this connection from the historical connections list it should still work. It you have many connections in the history list, clean it up so it is clear that you are selecting the correct one.
If you think this is not working correctly we need to look into it.
Back to top
ora-12538_001.GIF (attachment deleted)  
« Last Edit: 27.07.11 at 10:36:56 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1