ORA12154 (TNS could not resolve service name) from VBA (in Excel)

[MCubitt]

I get the ORA 12154 error on one machine...

Running the script gives the error, it's attempting to connect using

  connStr = "Driver={Microsoft ODBC for Oracle}; Server=IFSL; UID=username; PWD=lalala"

TNS names has:

IFSL.KEYMED.LOCAL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.56.4.10)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = IFSL)
    )
  )

and a tnsping works:

H:\>tnsping ifsl

TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 12-OCT-20
04 10:05:10

Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:
c:\oracle\ora92\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 10.56.4.10)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = IFSL)))
OK (190 msec)

The ping result suggests to me that the TNS is working correctly. It found the service name and host/port.

I am not using an existing data source, rather a dns-less process refering to the ODBC driver. This works on other machines.

The problem exists if I log on this PC as me or the user who has the problem. Both working and this machine have MS Windows 2000 (But this has SP2 and I have SP4) and Excel 2000 (9.0.4402 SR-1).

I will update to SP4... but in the meantime..

Does anyone have any ideas, please?

thanks





Applications Support
UK

 

[MCubitt]

Installing SP4 has not corrected the problem (it was a long shot, but worth a try!)




Applications Support
UK

 

[taupirho]

Dunno if this will work but in your connection string try putting the global name of the database as the server - typically this might be something like your_sid.world e.g in your case IFSL.world

 

[MCubitt]

Nice idea, but did not work.

I have been onto Metalink, they asked me trace using SQLNET but it didn't even seem to get that far for some reason!




Applications Support
UK

 

[MCubitt]

In case my code is dumb (though it works for other Pcs so .. ) here it is:

Dim oconn As Object
  Set oconn = CreateObject("ADODB.Connection")
  Set oRs = CreateObject("ADODB.Recordset")
  connStr = "Driver={Microsoft ODBC for Oracle}; Server=IFSL; UID=user; PWD=pass"
  oconn.Open connStr
  
  oRs.Open "SELECT Distinct fut.identity, fut.description FROM ifsapp.fnd_user_tab fut Where fut.active = 'TRUE' and fut.identity not like 'DEPT_%' and fut.active='TRUE' and fut.identity not in ('IFSAPP','CBSSERVER','IFSINFO','SS','SALESANDMARKETING','IFSAPPEN') ORDER BY fut.identity", oconn
    
  numrecs = 0

Applications Support
UK

 

[sem]

Try Server=IFSL.KEYMED.LOCAL. Check sqlnet.ora file for NAMES.DEFAULT_DOMAIN parameter on "working" and "not working" stations.

Regards, Dima

http://seminihin.narod.ru

 

[MCubitt]

Sem,

Actually I tried that too!

On working PC
NAMES.DEFAULT_DOMAIN = keymed.local

On not working PC
NAMES.DEFAULT_DOMAIN = keymed.local




Applications Support
UK

 

[Scunningham99]

have u also checked sqlnet.ora?



Sy UK

 

[sem]

Do you have only 1 Oracle Home on that box? And how about tracing at least to answer for sure that correct settings are used?

Regards, Dima

http://seminihin.narod.ru

 

[MCubitt]

Sy, yes..

SQLNET ora is identical on both PCs.


I added an System DSN called IFSL using the microsoft ODBC for Oracle driver, tried creating an external db query in Excel (MS Query) and it failed with the same ORA message. That rules out my script!




Applications Support
UK

 

[MCubitt]

Using the created DSN, the error has a little more..

Driver's SQLSetConnectAttr failed.





Applications Support
UK

 

[MCubitt]

Had a major problem, but solved it, so am posting here so that someone may benefit.
Attempting to create an ODBC DSN using Microsoft ODBC for Oracle (MSORCL32.DLL of 8/23/2001) on Windows XP (which comes with MDAC 2.7), get the following error:
"Driver's SQLSetConnectAttr failed"
Problem is that the ODBC settings expect Oracle 8.0 to be used by default. So change the registry to look for Oracle 9 DLLs and DSN creation succeeds.
Need to change 2 registry entries, see HKLM\Software\Microsoft\MSDTC\MTxOCI
and change OracleXaLib from oraclient8.dll to oraclient9.dll and change OracleSqlLib from orasql8.dll to orasql9.dll (note: if OracleOciLib is oci.dll do not change it).
(As a reference see outdated Microsoft article Q264012).



This solved it!! Sorry I did not mention (notice) an old 7.2 version on Oracle.





Applications Support
UK

 

[KenCunningham]

Thanks for posting the solution - always nice to tie things up. Have a star!

 

[MCubitt]

sem, seems you were on the right trial but I discovered the problem by the posting above (from another site).




Applications Support
UK

 

[MCubitt]

Ken,

Thanks for the star and you are so right about tying up the loose ends. There's nothing wrong than searching for solutions then to find a million similar questions that have no end!

The file names were slightly different for me but the theory was the same.

Many of our machines have Oracle 7.2 loaded (legacy system) and don't fail so a real mystery really.. the wonders of OUI I suspect!



Applications Support
UK