Connectivity to Oracle 12.2 instance via OLE DB ?

• VoldemarG

  Hall of Fame

  Points: 3883

  More actions

  February 28, 2020 at 8:01 pm

  #3729712

  I installed Oracle 12 on Windows 10 in  all possible ways/configurations, including the defaults.

  I installed Oracle Client, Instant Client, and ODAC - in all possible permutations of each of these 3.

  Still no way to connect via OLE DB (from Excel, SSIS, SSRS, -..anything). No luck.

  Using the correct name from TNSNames and all. or IP.  same problem: when entering info to OLEDB connection prompt, getting error 'Server Name cannot be found.'.

  I went through 1000 forums posts, tried 1000 things. No connection.

  Apparently, there is something basic, fundamental, that I may not know or not aware of.  I also reinstalled Oracle instances about 20 times. tried 12, 19,  other versions...   still same problem.

  Is there still hope for me ?

   

   

  Likes to play Chess

• frederico_fonseca

  SSCoach

  Points: 16215

  More actions

  February 28, 2020 at 9:08 pm

  #3729729

  first - did you install the 32 bit version of the client

  second - did you setup a tns name entry for it

  third - if you do "tnsping oraclesid" does it work or does it fail?

  fourth - what oledb client are you selecting on your connection?

• VoldemarG

  Hall of Fame

  Points: 3883

  More actions

  March 1, 2020 at 2:34 pm

  #3729962

  1. I believe it was 64 bit  Should it be 32?...
  2. TNSNames file has an entry for server named ORCL, I did not manually set up anything additional . Should I?
  3. TNSPING works fine.
  4. OLE DB PROVIDER FOR ORACLE.

  Likes to play Chess

• frederico_fonseca

  SSCoach

  Points: 16215

  More actions

  March 1, 2020 at 2:47 pm

  #3729963

  to "develop" the SSIS package you need 32bit versions of the clients as Visual Studio is a 32 bit application.

  you can have both 32 and 64 bit versions of the client installed - but you will either need to setup a global TNS_ADMIN entry or maintain  both tnsnames.ora files in each client folder

  once you install the correct driver (and make sure that during the installation you select the odbc/oledb parts - not installed by default) you should see "oracle provider for OLE DB" as an available option when adding a new connection. (not the Microsoft one!!)