Invalid column definition error when using four part name to access Oracle DB as SQL Server linked server

Question

I have setup a linked server in SQL Server 2008 R2 in order to access an Oracle 11g database. The MSDASQL provider is used to connect to the linked server through the Oracle Instant Client ODBC driver. The connection works well when using the OPENQUERY with the below syntax:

SELECT * 
FROM OPENQUERY(LINKED_SERVER, 'SELECT * FROM SCHEMA.TABLE')

However, went I try to use a four part name using the below syntax:

SELECT * 
FROM LINKED_SERVER..SCHEMA.TABLE

I receive the following error:

Msg 7318, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "LINKED_SERVER" returned an invalid column definition for table ""SCHEMA"."TABLE"".

Does anyone have any insight on what my be causing the four part name query to fail while the OPENQUERY one works without any problems?

Answer 1

The correct path to follow is to use OPENQUERY function because your linked server is Oracle: the four name syntax will work fine for MSSQL servers, essentially because they understand T-SQL.

With very simple queries, a 4 part name can accidentally work but not often if you are in a real scenario. In your case, the SELECT * is returning all the columns, and in your case one of the column definition is not compatible with SQL Server. Try another table or try to select a single simple column (e.g. a CHAR or a NUMBER), maybe it will work without problem.

In any case, using distributed queries can be tricky sometime. Database itself does some optimizations before executing commands, so it is important for the database to know what it can do and what it can't. If the DB thinks the linked server is MSSQL, it will take some action that may not work with Oracle.

When using four part name syntax with a linked DB different from MSSQL, you will have other problems as well, for example using database builtin functions (i.e. to_date() Oracle function will not work because MSSQL would want to use its own convert() function, and so on).

So again, if the linked server is not a MSSQL, the right choice is to use OPENQUERY and passing it a query that use a syntax valid against the linked server SQL dialect.