Cannot Query Oracle Linked Table

• scott.bond

  SSC Rookie

  Points: 43

  More actions

  September 11, 2012 at 8:13 pm

  #393158

  SQL Server 2005 on Windows 7 box. I have the Oracle 11g client installed. I also have an ODBC connection to the Oracle DB and that works fine - used it in Access with no issue. Also can see linked Oracle tables in my SQL Server.

  I have tried my query with several types of linked tables including MSDASQL - the error I get from this query is (where X is my linked server name):

  SELECT * FROM OPENQUERY(X, 'SELECT * FROM TABLE')

  OLE DB provider "MSDASQL" for linked server "X" returned message "[Microsoft][ODBC Driver Manager] Invalid connection string attribute".

  OLE DB provider "MSDASQL" for linked server "X" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".

  Msg 7303, Level 16, State 1, Line 3

  Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "X".

  I have tried selecting and deselecting "Allow in process" and every other solution I can find.

  PLEASE HELP - I AM BANGING MY HEAD ON THE DESK!

• anissw

  SSC Enthusiast

  Points: 117

  More actions

  March 24, 2013 at 7:57 pm

  #1600155

  Hi Scott- Have you tried MSDAORA for Oracle?

• Jo Pattyn

  SSC-Dedicated

  Points: 32415

  More actions

  June 15, 2014 at 12:39 pm

  #1721801

  sqlserver works best with the oracle oledb driver (OraOLEDB)