ORA-12154 TNS could not resolve service name through Automa

From: ShadowAssassin <tariq.khan_at_gmail-dot-com.no-spam.invalid>
Date: Tue, 15 Nov 2005 02:11:21 -0600
Message-ID: <w72dnflGm4u0BeTeRVn_vA@giganews.com>

Hello!

Oh no! not another 12154! This one is different, I promise, and I would really be grateful for any help!

MY QUESTION:

I can't connect to my Oracle9i database when the connection request is made via automation, i.e. I have Visual Basic call another dll which inturn makes an ODBC connection request. (Making direct connections on the other hand works.)

When I try I get the following error message:
> state was 08004, code 12154, message [Oracle][ODBC][Ora]ORA-12154:
TNS:could not resolve service name[ODBC][Ora]ORA-12154: TNS:could not resolve service name[/quote:0498496778]
What could be wrong?

ADDITIONAL INFO:

In my case, the connection request takes place in the following manner:
Visual Basic --> (D)COM service for R --> R --> DLL --> ODBC API --> Oracle API --> SQL*NET --> SQL Listener --> database
* R is an open source programming language for statistics

MAKING DIRECT CONNECTION REQUESTS WORK
I can connect fine using sqlplus; tnsping works; opening in TOAD works. I can connect to the DB directly using VB or using R.

My TNSNAMES.ora:

          FPL_DATA =
              (DESCRIPTION =
              (ADDRESS_LIST =
                       (ADDRESS = (PROTOCOL =

TCP)(HOST = server.name.removed)(PORT =
1521))

               )
              (CONNECT_DATA =
                       (SERVICE_NAME = DATA001)
               )
               )

sqlnet.ora contains:
[code:1:0498496778]            
SQLNET.AUTHENTICATION_SERVICES=(none)
              NAMES.DIRECTORY_PATH=

(TNSNAMES)[/code:1:0498496778]

When i tnsping, I can see that its accessing the correct file and I made sure I removed all other files with the same name on my local drive. I checked that the port works through telnet: fine.

SECURITY ISSUES?
I thought that the context and security issues might have something to do with it. The automation is run through a (D)COM service, and I have checked through dcomcnfg that the process is running as me. I have also made sure 'Everyone' as access to C:\oracle\ora92\ and all of the relevant subdirectories. Registry entries are similarly free-for-all so that anybody would have sufficient priviledges.

OTHER DRIVERS TO TRY?
When I try using the Microsoft Oracle dataprovider I get a generic error:
> state was NA000, code 6413, message [Microsoft][ODBC driver for
Oracle][Oracle]ORA-06413: Connection not open.[ODBC driver for Oracle][Oracle]ORA-06413: Connection not open.[/quote:0498496778]

SQL NET TRACE FILE:
There are two tracefiles. The first one is exactly the same as when you have a successful connection. I have used the tracefiles to prove that querying of the tnsnames.ora file is successful. Here is an excerpt:
> (3124) [11-NOV-2005 16:29:22:352] nnftqnm: Using tnsnames.ora
address (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = server.name.removed)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DATA001))) for name FPL_DATA nnftqnm: Using tnsnames.ora address (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = server.name.removed)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DATA001))) for name FPL_DATA[/quote:0498496778]

The Second file contains:
> (3720) [11-NOV-2005 17:12:55:442] --- LOG CONFIGURATION INFORMATION
ENDS ---
>
> (3720) [11-NOV-2005 17:12:55:442] ntvllt: entry
> (3720) [11-NOV-2005 17:12:55:442] ntvllt: exit
> (3720) [11-NOV-2005 17:12:55:442] nigini: entry
> (3720) [11-NOV-2005 17:12:55:442] nigini: Count in NI global area
now: 1
> (3720) [11-NOV-2005 17:12:55:442] nigini: Count in NI global area
now: 1
> (3720) [11-NOV-2005 17:12:55:442] nrigbi: entry
> (3720) [11-NOV-2005 17:12:55:442] nrigbni: entry
> (3720) [11-NOV-2005 17:12:55:442] nrigbni: Unable to get data from
navigation file tnsnav.ora
> (3720) [11-NOV-2005 17:12:55:442] nrigbni: exit
> (3720) [11-NOV-2005 17:12:55:442] nrigbi: exit
> (3720) [11-NOV-2005 17:12:55:442] nigini: exit
> (3720) [11-NOV-2005 17:12:55:442] niqname: Hst is already an
NVstring.
> (3720) [11-NOV-2005 17:12:55:442] niqname: Inserting CID.
> (3720) [11-NOV-2005 17:12:55:442] nigtrm: Count in the NI global
area is now 0 [## A successful log-in is different from here on - should say 'niotns: entry' ##]
> (3720) [11-NOV-2005 17:12:55:442] nrigbd: entry
> (3720) [11-NOV-2005 17:12:55:442] nrigbd: exit
> (3720) [11-NOV-2005 17:12:55:442] nigtrm: Count in the NL global
area is now 0 nigtrm: Count in the NL global area is now 0[/quote:0498496778]
I am pretty certain the problem occurs BEFORE a TCP request but AFTER the alias has been successfully resolved from the TNSNAMES.ora file. I dont care how to get it to work, i just want to see that beautiful connection come together! Experts, Please help :-)!!

-Tariq Received on Tue Nov 15 2005 - 02:11:21 CST