ORA-12154 Error using ODBC on Win2k
Warren Wright
I am trying to SQLDriverConnect to our Oracle Server, which resides on
another machine in the network.
I believe I have the ODBC connection set up correctly, as clicking
"Test Connection" and then entering a valid username/password connects
successfully.
Here is the tnsnames.ora file from my client machine:
CSDWORLD.ENA.US.EXPERIAN.LOCAL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = w4533071)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = csdworld)
)
)
As you can see, the remote host is w4533071, which I can ping, etc
from my box, and I can connect to that remote database using the
various Oracle tools like SQL Plus or Enterprise Manager Console.
Here is the relevant ODBC trace section:
---------------------------------------------------
SRV40 98c-588 ENTER SQLDriverConnectW
HDBC 019D2188
HWND 00000000
WCHAR * 0x01997310 [ -3] "******\ 0"
SWORD -3
WCHAR * 0x01997310
SWORD 8
SWORD * 0x00000000
UWORD 0 <SQL_DRIVER_NOPROMPT>
SRV40 98c-588 EXIT SQLDriverConnectW with return code -1
(SQL_ERROR)
HDBC 019D2188
HWND 00000000
WCHAR * 0x01997310 [ -3] "******\ 0"
SWORD -3
WCHAR * 0x01997310
SWORD 8
SWORD * 0x00000000
UWORD 0 <SQL_DRIVER_NOPROMPT>
DIAG [08004] [Oracle][ODBC][Ora]ORA-12154: TNS:could not resolve
service name
(12154)
DIAG [IM006] [Microsoft][ODBC Driver Manager] Driver's
SQLSetConnectAttr failed (0)
---------------------------------------------------
If it matters, my test application is written in Visual C++ 6.0,
latest service pack.
Oracle version 9.2 (I believe).
Please get me started, and I look forward to trying your suggestions.
Regards,
Warren Wright
Dallas, TX
Randy Harris
"Warren Wright" <warren...@us.scorex.com> wrote in message
news:8497c269.04041...@posting.google.com...
> Please help point me in the right direction.
>
> I am trying to SQLDriverConnect to our Oracle Server, which resides on
> another machine in the network.
>
> I believe I have the ODBC connection set up correctly, as clicking
> "Test Connection" and then entering a valid username/password connects
> successfully.
>
> Here is the tnsnames.ora file from my client machine:
>
> CSDWORLD.ENA.US.EXPERIAN.LOCAL =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = w4533071)(PORT = 1521))
> )
> (CONNECT_DATA =
> (SERVICE_NAME = csdworld)
> )
> )
It's very likely that your client is not adding the domain name to your net
service alias. Create another entry in your client tnsnames.ora, exactly
like the one above, but with only "CSDWORLD =" on the first line. Like
this:
CSDWORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = w4533071)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = csdworld)
)
)
HTH,
Randy
Warren Wright
error message. Further, if I modify my client code to include
"DBQ=CSDWORLD" or "DBQ=CSDWORLD.ena.us.exeperian.local" it has no
effect.
Obviously the tns entry is effective, as when I test it from the ODBC
setup in Administrative Tools for Win2k I successfully connect to the
server, and can see in the network/log/listener.log (on the server)
where it has connected.
Do I need a listener of any kind on the client machine to help my C++
application find the local tns entry when it uses ODBC?
It just seems like everything is already set up as it logically should
work, but then again I'm fairly new to Oracle, so any advice is
appreciated.
Regards,
Warren Wright
Dallas
"Randy Harris" <ra...@SpamFree.com> wrote in message news:<uCFfc.2$mh...@newssvr16.news.prodigy.com>...
Randy Harris
"Warren Wright" <warren...@us.scorex.com> wrote in message
news:8497c269.04041...@posting.google.com...
> error message. Further, if I modify my client code to include
> "DBQ=CSDWORLD" or "DBQ=CSDWORLD.ena.us.exeperian.local" it has no
> effect.
>
> Obviously the tns entry is effective, as when I test it from the ODBC
> setup in Administrative Tools for Win2k I successfully connect to the
> server, and can see in the network/log/listener.log (on the server)
> where it has connected.
>
> Do I need a listener of any kind on the client machine to help my C++
> application find the local tns entry when it uses ODBC?
I'm quite surprised that did not help. There should be no need for a
listener on the client system, the listener is used only on the server. The
fact that you can get to the DB with SQL*Plus confirms that the listener is
functional on the server. The listener is the "facilitator". It listens
for incoming service requests and provides connection ID information for a
server service to the client.
The specific error message that you are getting is usually quite telling of
the problem - "TNS:could not resolve service name". The service name is
that first line in the tnsnames.ora file. For some reason that I am at a
loss to explain, your client is not finding that entry. I can think of only
two other possible explanations. Either the client app isn't really using
CSDWORLD as the service name or it isn't using that particular tnsnames.ora
to resolve the service name.
Check the client system to make sure you don't have an environment variable
that starts with TNS, such as TNS_ADMIN_DIR, or one called TWO_TASK or one
called LOCAL. Also do a find on the entire system to see if there might be
another tnsnames.ora file somewhere. I've seen that cause problems when
there has been more than one version of Oracle installed on a system.
Randy