-1

I am trying to connect to Oracle ODBC for OraClient10g_home1 in System DSN.once done,i have tested the connection and it says connection successful.but when i try to import Oracle data from Excel's built-in function (Microsoft Query),it gives me the following error message.please let me know how to fix this issue.

Oracle Database Server - 32 Bit,Operating System - Windows 10(64-Bit),Excel Office 2010 (32-Bit)

error

Iftekhar
  • 335
  • 2
  • 11
  • 26
  • please check your entry in tnsnames.ora file location in ORACLE_HOME/network/admin path. correct this and in windows cmd using tnsping check its status (tnsping yourentry) if this works odbc also will work – mohsen.b Nov 03 '16 at 10:30
  • thanks for the reply.tried doing tnsping like this on cmd tnsping D:\oracle\product\10.2.0\client_2\network\admin and it displays this : TNS-03505: failed to resolve name – Iftekhar Nov 03 '16 at 10:42
  • did you check tnsnames.ora file in D:\oracle\product\10.2.0\client_2\network\admin. is there your entry in tnsnames.ora file. – mohsen.b Nov 03 '16 at 10:47
  • what entry are you talking about actually?i did check tnsnames.ora file and tried this on cmd tnsping 172.16.1.23 which is being the host and the reply is OK(0 msec) – Iftekhar Nov 03 '16 at 10:52
  • I assume your Excel is 32 bit. Did you create the DSN also in the 32 bit ODBC Administrator? Otherwise it does not work. – Wernfried Domscheit Nov 03 '16 at 19:53
  • Yes i created the DSN.tested the connection.successful it says but still no luck.the error message persists.there must be something silly i am not being able to find – Iftekhar Nov 03 '16 at 19:59

1 Answers1

0

in tnsnames.ora you should have an entry for database instance located in target server like :

Test=
 (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST=target server)(PORT=1521))
    )
    (CONNECT_DATA =
      (SID = orcl)
    )
  )

then tnsping Test in cmd should work fine. you will use this tns entry in your odbc.

TallTed
  • 9,069
  • 2
  • 22
  • 37
mohsen.b
  • 436
  • 2
  • 8