SSAS And Oracle - Can't connect to the data source

• Simon Facer

  SSCertifiable

  Points: 5427

  More actions

  December 10, 2008 at 1:45 pm

  #202757

  We're bringing up a new SSAS 2005 instance, and the user needs to connect to an Oracle server for the Data Source.

  Oracle: 1og Enterprise, 64-bit, Path: 10.2.0.3

  SQL: 2005, Enterprise, 64-bit, Patch: SP2 CU10

  I've tried the 64-bit Oracle 10.2.0.1 Client, 32-bit 10.2.0.1 Client. Nothing works. I currently have the 32-bit client installed, and can connect to the Oracle server with SQLPlus, but not with an ODBC connection, a UDL file or through an SSAS data source.

  I'm an Oracle novice, and I'm all out of ideas as to what to do to get this resolved,. I have a ticket open with MS, but they don't seem to be helping a whole lot.

  My user is about to start pulling his hair out, and then he's going to move on to me if I dont get this resolved ...

• Simon Facer

  SSCertifiable

  Points: 5427

  More actions

  December 10, 2008 at 9:35 pm

  #910816

  It was apparently a known bug in the Oracle driver - both BIDS and SSMS are 32-bit apps and need the 32-bit client, but the 32-bit client can't handle special characters in paths. In the case of the 32-bit apps, they are installed to the '\Program Files (X86)' folder with '(' and ')' characters. Running either of these from a desktop / start menu icon runs them from the \...(x86) folder, I created a bat file to execute (for SSMS)

  CD \Progra~2\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE

  SQLWB

  or (for BIDS)

  CD \Progra~2\Microsoft Visual Studio 8\Common7\IDE

  DEVENV

  not \Progra~1 refers to '\Program Files', without the '(x86)'.

  Then it was just a case of installing the 64-bit drivers for SSAS to use, and Bob's You Uncle ... all fixed.