Cannot Query Oracle Linked Table

  • scott.bond

    SSC Rookie

    Points: 33

    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

    Hi Scott- Have you tried MSDAORA for Oracle?

  • Jo Pattyn

    SSC-Dedicated

    Points: 31300

    sqlserver works best with the oracle oledb driver (OraOLEDB)

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply