debugMinor
Sql Server 2008 x64 ODBC Linked Server to Oracle Not Working
Viewed 0 times
odbc2008x64sqlworkingserveroraclelinkednot
Problem
I have an install of Sql Server 2008 x64, installed on two boxes. One is a Win 7 x64 workstation (Sql Server 2008 x64 SP2 developer edition), the other is a Windows Server 2003 x64 (Sql Server 2008 x64 SP2 Enterprise edition). I am trying to create a linked server to an external vendor's Oracle instance. I have installed the Oracle ODAC 11g 64 bit drivers using the full install (not the XCopy version).
The drivers appear to have all been installed correctly. I have created and updated my tnsnames.ora file using the correct IP, port, etc to the remote server and rebooted. Using the 64 ODBC admin tool, I am able to create the ODBC connection to the Oracle server and the "Test" button returns as successful using the alias in the tnsnames.ora file and the correct user ID and password.
I then go in to Sql Server 2008 and try to create my linked servers. I can create the OLE DB linked server and it connects successfully, I can list the tables/views in the catalog and do queries against them with only one significant problem. For tables with TIMESTAMP fields, a normal 4-part query throws fits. Looking around this appears to be a common problem with OLEDB linked servers from Sql Server to Oracle and using OPENQUERY is the most common workaround, which I do have working.
The ODBC connection, which is what our vendor recommends using to connect to them, is where I have big problems. I can create the linked server, which appears to be successfully created using the System DSN ODBC connection I created earlier. I can view the lists of tables/views in the catalog and they all appear to show up correctly. However, when I try to get data, it fails completely.
If I try to right click on a view name and select Script -> SELECT To.... I get a message that says that:
[tablename] contains no columns that can be selected or the current
user does not have permissions on that object.
If I try to script out a SELECT manually and run it, as I know most of the column
The drivers appear to have all been installed correctly. I have created and updated my tnsnames.ora file using the correct IP, port, etc to the remote server and rebooted. Using the 64 ODBC admin tool, I am able to create the ODBC connection to the Oracle server and the "Test" button returns as successful using the alias in the tnsnames.ora file and the correct user ID and password.
I then go in to Sql Server 2008 and try to create my linked servers. I can create the OLE DB linked server and it connects successfully, I can list the tables/views in the catalog and do queries against them with only one significant problem. For tables with TIMESTAMP fields, a normal 4-part query throws fits. Looking around this appears to be a common problem with OLEDB linked servers from Sql Server to Oracle and using OPENQUERY is the most common workaround, which I do have working.
The ODBC connection, which is what our vendor recommends using to connect to them, is where I have big problems. I can create the linked server, which appears to be successfully created using the System DSN ODBC connection I created earlier. I can view the lists of tables/views in the catalog and they all appear to show up correctly. However, when I try to get data, it fails completely.
If I try to right click on a view name and select Script -> SELECT To.... I get a message that says that:
[tablename] contains no columns that can be selected or the current
user does not have permissions on that object.
If I try to script out a SELECT manually and run it, as I know most of the column
Solution
If I try to right click on a view name and select Script -> SELECT To.... I get a message
The GUI is probably trying to access an Oracle Data Dictionary View (in order to read the column names & data types) that it does not have permission to select from, hence the error message.
Ask the Vendors Oracle DBA to trace the ODBC session so that you can track down the exact cause.
The GUI is probably trying to access an Oracle Data Dictionary View (in order to read the column names & data types) that it does not have permission to select from, hence the error message.
Ask the Vendors Oracle DBA to trace the ODBC session so that you can track down the exact cause.
Context
StackExchange Database Administrators Q#20327, answer score: 2
Revisions (0)
No revisions yet.