Solved

Oracle DB lookup error - TNS could not resolve the connect identifier specified

Posted on 2013-10-28
29
2,323 Views
Last Modified: 2013-11-16
Hi,

I am currently trying to find the cause of an error in some code that I did not write. This code was developed as a way to run an Oracle DB lookup to simplify data entry into a Kofax Ascent Capture system. I am not a coder myself so I am struggling to find the real issue. They have a working system on Windows XP that uses this same dll, and they have recently upgraded some workstations to Windows 7 64-bit. They have installed the 32-bit Oracle client, and named the DSN the same name as it was in XP. A dll for the lookup gets registered on the system called "FieldLookup", and I have some error catching set up in the validation script to tell me on what line the error happens. The error is specified on line 368, and when I go to this line I see this: "oFieldLookup.Init"

There are two errors that are accompanied that say "ERROR [08004][Oracle][Ora]ORA-12154: TNS: could not resolve the connect identifier specified

Error [IM006][Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed"

I have opened the dll in Visual Studio, and I can see the Init in the FieldLookup.dll, but I don't know how to open this to see what it does to figure out if there is something in it that is configured incorrectly or what.


I am referencing this article because it seems like it could be the solution. I believe that I found the Oracle home directory, but the tnsnames.ora file and the others that that the article speaks of is not in it. Could that be the whole problem? I just don't know enough about Oracle to know if that is what I need to resolve this error.

https://forums.oracle.com/thread/2506724


Any help is appreciated.

Thanks,

Whah
0
Comment
Question by:Whah
  • 18
  • 11
29 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39607196
After you install the correct Oracle client with appropriate data access drivers, you need to manually set up the tnsnames.ora and possible the sqlnet.ora files.

Should be able to copy it from the working XP system.

To do this, you need information from the database server itself (It should also have a copy of the tnsnames.ora file that you can copy).



Do you have a DBA for the system?
0
 

Author Comment

by:Whah
ID: 39607199
Upon closer examination it appears that the tnsnames.ora and sqlnet.ora files are where they should be, and are in the same location as they are on the working XP machine. I found them here.
C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN

Thanks again,

Brian
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39607201
I would also suggest you use Oracle's ODBC drivers, not Microsoft's...
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39607205
>>and are in the same location as they are on the working XP machine

But, do they contain the same entries?  Copy the one's from the XP machine.
0
 

Author Comment

by:Whah
ID: 39607210
They do have a DBA, but they think this error is related to where the DLL is located. They say that they have seen this error before, and this that it is due to it being in the (x86) directory.
0
 

Author Comment

by:Whah
ID: 39607218
Ok, I copied the ones from the XP machine, and I am still getting the same error, so I have to believe those are correct now.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39607222
The DBA can make sure you have the correct entries in the tnsnames.ora file.  I forgot you had a working XP machine so no need to involve the DBA yet.

I don't think this is the issue right now but another thing to look at is the Bit version of ODBC you created the DSN with.

In a 64Bit Windows setup, there are two.  The 32Bit one is in the syswow64 folder:
http://support.microsoft.com/kb/942976
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39607225
>> I copied the ones from the XP machine, and I am still getting the same error

Form a command prompt, see if you have tnsping (depending on what client/options you installed, you may or may not have it):
tnsping ORACLE_SID

where ORACLE_SID is the database alias inside the tnsnames.ora file you are trying to connect with.
0
 

Author Comment

by:Whah
ID: 39607229
I did create the DSN from the syswow64 folder, because I know that Kofax only supports 32-bit ODBC.

Thank you very much for the help, slightwv.
0
 

Author Comment

by:Whah
ID: 39607236
Here is the result of that.


Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\admin>tnsping ORACLE_SID

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 28-OCT-2
013 15:41:41

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora

TNS-03505: Failed to resolve name
0
 

Author Comment

by:Whah
ID: 39607239
oops sorry. I didn't read that last line. I will try again.
0
 

Author Comment

by:Whah
ID: 39607253
Now I am getting this.

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = intradb)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = TAGUS)))
OK (0 msec)
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39607262
OK, now we know Oracle networking is set up correctly.  Hence the "OK (0 msec)"

From the ODBC Admin, can you 'Test' the connection?
0
 

Author Comment

by:Whah
ID: 39607267
I have, and it does test successfully.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39607283
If you've tested the DSN and it works but the app fails, then the app must not be using the  DSN entry you think it is using.

Just for grins, do you have a 32Bit Access installed or some other 32Bit app that can easily use the same DSN?
0
 

Author Comment

by:Whah
ID: 39607310
The DSN names do match, and they are simply named "Oracle" on both the working an non-working stations.  

It doesn't appear that they have Access installed. Any ideas for something that I can try with the DSN other than that?


Thanks again.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39607313
Check the drivers for the DSN.  The name of the DSN doesn't matter.

I still suggest you use the native Oracle ODBC drivers instead of the Microsoft ones.

When you installed the Oracle Client, did you install the ODBC drivers?
0
 

Author Comment

by:Whah
ID: 39607330
I'm going to have to check with my contact there. He was the one that installed the driver, and I believe he installed everything.
0
 

Author Comment

by:Whah
ID: 39609049
I was able to confirm that we are using the Oracle ODBC drivers.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39609061
Can you reconfirm your error?  I'm far from an Expert in ODBC but the original error posted seems to show the Microsoft driver:  [Microsoft][ODBC Driver Manager]
0
 

Author Comment

by:Whah
ID: 39609071
No problem. Here are the errors that I am getting. The red box is on line 368. Thanks
0
 

Author Comment

by:Whah
ID: 39609087
Oracle driver
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 39609110
Thanks for that.

I missed the first message in the original post and focused on the second.

I'm running out of ideas.

Make sure there is only one Oracle product installed or ODBC is looking in the correct HOME for the tnsnames.ora file.

The only other thing I can think of is an incompatibility issue.  I don't believe 10g is certified to run on Windows 7 until I think 10.2.0.5.  Don't quote me on that.  I'm going from memory.
0
 

Author Comment

by:Whah
ID: 39609120
Ok, I will check on that. I really appreciate all the help, slightwv. Thanks.
0
 

Author Comment

by:Whah
ID: 39609205
I have found a couple links that may support your compatibility theory.

This one says to use the Oracle 10g Release 2 for Windows Vista, but it's from 2010. https://forums.oracle.com/message/4108968

And this one says that the earliest supported version for Windows 7 is Oracle 11g release 2. http://kb.iu.edu/data/axju.html
0
 

Author Comment

by:Whah
ID: 39609220
I am now thinking that these may be related to installing the database software itself and not the ODBC driver version.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39609456
>> may be related to installing the database software itself and not the ODBC driver version.

Oracle can be confusing at times.  It was client/server long before client/server was a hot topic.

As long as the ODBC drivers are installed, it doesn't matter if they were installed as part of he 'server install' or not.  The Oracle server comes with the client built in.

Now, if you have a separate client installed on the same machine, you need to make sure that each client refers to the correct installation folder for it's config files.

Typically every Oracle install has it's own copy of the tnsnames.ora file.  If you want a 'shared' version, you can set an environment variable TNS_ADMIN that points to the folder containing the common tnsnames.ora file.
0
 

Accepted Solution

by:
Whah earned 0 total points
ID: 39639763
Hi slightwv,

The resolution to this issue was to upgrade the Oracle client software version to something at or beyond 10.2.0.3 as per this article http://social.msdn.microsoft.com/Forums/en-US/fa3e362f-81d8-4667-899f-6d417dca96f1/data-source-provider-error-microsoftodbc-driver-manager-drivers-sqlsetconnectattr, and also making sure that the tnsnames and sqlnet.ora files are in the proper location.

Thank you again for all of your help.

Brian
0
 

Author Closing Comment

by:Whah
ID: 39653039
The reason that my own comment was selected was because I was able to finally find a post that specifically mentioned both errors, and the recommended course of action in that article ultimately fixed this issue for me.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle 12c 10 100
SQL query of Oracle 10g database. 8 56
Action link in Union Reports Not Working in OBIEE 11g 1 19
dates - loop 12 40
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now