0

Using the Oracle client:

C:\temp> tnsping usrrprd

TNS Ping Utility for 32-bit Windows: Version 12.2.0.1.0 - Production on 06-JUL-2018 10:09:51

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

Used parameter files:
C:\Oracle12\product\12.2.0\client_1\network\admin\sqlnet.ora

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = crs-rrdw-prd.tdbank.ca)(PORT = 1535)) (CONNECT_D
ATA = (SERVER = DEDICATED) (SERVICE_NAME = USRRPRD.tdbank.ca)))

I can access the server fine using sqlplus linqpad, sqldeveloper and even Visual Studio. Excel is stumping me, however. Using Microsoft Query and the Microsoft ODBC for Oracle Connect, I get:

enter image description here

Using the Data Connection Wizard, it connects and displays the tables and views. However, when I try to retrieve one, I get a different error:

enter image description here

Honestly, I'd take either approach, though since the table I want to see is large, Microsoft Query might be preferable.

How can I resolve these errors?

user1443098
  • 6,487
  • 5
  • 38
  • 67
  • Did you install ODBC and/or OLE DB driver? linqpad and Visual Studio may use "Oracle Data Provider for .NET" which is different (see [How to connect to Oracle 11 database from . net](https://stackoverflow.com/questions/34803106/how-to-connect-to-oracle-11-database-from-net/34805999#34805999)). SQL Developer is Java based which is again different. Is the Oracle Client the same architecture (i.e. 32-bit or 64-bit) as your Excel? – Wernfried Domscheit Jul 06 '18 at 14:46
  • Perhaps your Excel does not find your `sqlnet.ora` file. Try to set an environment variable `TNS_ADMIN=C:\Oracle12\product\12.2.0\client_1\network\admin` – Wernfried Domscheit Jul 06 '18 at 14:48
  • @WernfriedDomscheit Control panel doesn't report what drivers are installed. Where should I look (it was installed a while ago and I don't remember). I have both 32-bit and 64-bit clients installed. – user1443098 Jul 06 '18 at 15:02
  • Have a look at your registry or look for folders `...\client_1\oledb`. ODBC drivers you should see in ODBC Adminstrator (`C:\Windows\System32\odbcad32.exe` (64-bit) and `C:\Windows\SysWOW64\odbcad32.exe` (32-bit)) – Wernfried Domscheit Jul 06 '18 at 15:12
  • @WernfriedDomscheit Yeah, they are both are just where you said! thanks for the map. – user1443098 Jul 06 '18 at 15:23
  • @WernfriedDomscheit Your tip about setting TNS_ADMIN seemed to help as well! What I learned though is that patience is a virtue. Excel apparently tries to enumerate the database before giving you a chance to enter a query. Bit of a drag but at least I know more. – user1443098 Jul 06 '18 at 15:30

0 Answers0