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 names, I get an error message:
The OLE DB provider "MSDASQL" for linked server [linked server name]
returned an invalid column definition for table [table name].
The vendor states that the user ID (the same one in both cases) has the proper rights to the tables/views, which it appears to as the OLE DB connection mostly works. The Oracle server is 10g, but I don't know if it's 32 or 64 bit. Would that make a difference?
Right now I'm working on getting this working from the Win7 x64 workstation, but a short test on the 2003 server yielded the same results. If I have to, I guess I can make the OLEDB/OPENQUERY solution work. However it's not ideal or recommended by our vendor. Any ideas what I might be missing on getting the ODBC connection working?
Best Answer
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.