SQL Server: ODBC Linked Server to Oracle does not return all rows

Question

Let's say we have one SQL Server instance on one side:

• SQL Server 2008 R2 SP3
• Oracle 11.2 client installed with OLEDB Drivers for ODBC

.. and an Oracle database instance on another side:

• Oracle Database 11.1.0.7 PSU 24
• InstanceName: MERCURE

I created two database links on the SQL Server instance:

1. MERCURE (using ODBC Server DSN and MSDASQL Provider)
2. MERCURE_OLE (using OraOLEDB.Oracle)

On the Oracle instance (with instance name MERCURE), I created a schema and table as follows:

CREATE USER JEL_PERF_TST identified by "XXX";
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE to JEL_PERF_TST;
ALTER SESSION SET CURRENT_SCHEMA=JEL_PERF_TST;
-- select DEFAULT_TABLESPACE from dba_users where username='JEL_PERF_TST';
-- USERS
-- create a table for testing (Taken from https://use-the-index-luke.com/sql/example-schema/oracle/3-minute-test)
CREATE TABLE TestTbl (
 id NUMERIC NOT NULL,
 date_column DATE,
 a NUMERIC,
 b NUMERIC,
 text VARCHAR(255),
 state CHAR(1),
 PRIMARY KEY (id)
);
 INSERT INTO TestTbl
 SELECT level
 , SYSDATE - level
 , MOD(level,1234)
 , TRUNC(DBMS_RANDOM.VALUE(1, 10))
 , DBMS_RANDOM.STRING('l', 20)
 , DECODE(MOD(level, 5), 'X', 'A')
 FROM dual
CONNECT BY level <= 50000;

So, basically, I have 50.000 rows in my table. Now, let's run the following queries on SQL Server instance:

First, let's activate statistics:

set statistics io on;
set statistics time on;

Then, run a first query using OraOLEDB.Oracle provider:

SELECT * FROM [MERCURE_OLE]..[JEL_PERF_TST].[TESTTBL];

Here is what I got:

SQL Server parse and compile time: 
 CPU time = 32 ms, elapsed time = 274 ms.
(50000 row(s) affected)
 SQL Server Execution Times:
 CPU time = 2199 ms, elapsed time = 5769 ms.

Performances aren't pretty good (more tha 8.5 ms for a single row) but it doesn't matter here. We have 100% of rows returned.

Let's now run the query using OPENQUERY and same Linked Server:

SQL Server parse and compile time: 
 CPU time = 0 ms, elapsed time = 103 ms.
(50000 row(s) affected)
 SQL Server Execution Times:
 CPU time = 2542 ms, elapsed time = 6716 ms.

Same here, performances are bad but we have 100% of rows returned.

Now, let's run the exact same query, but using MSDASQL provider:

select * from openquery(MERCURE,'select * from JEL_PERF_TST.TestTbl');

It results in:

 SQL Server parse and compile time: 
 CPU time = 15 ms, elapsed time = 107 ms.
(**49901** row(s) affected)
 SQL Server Execution Times:
 CPU time = 1357 ms, elapsed time = 3932 ms.

Timing is better, but where are my 99 missing rows ??

I've tried many changes to my ODBC connection descriptor (Enable/disable result sets, raise the Fetch Buffer Size, Use MSDTC or not, etc.). Nothing worked.

Any clue ?

• Dmitry: We also lost 99 records from database using ODBC. Look here: stackoverflow.com/questions/4454695/…

Answer 1

Microsoft fixed this issue in Cumulative Update 4 for SQL Server 2014.

https://support.microsoft.com/help/2993262/

If you are unable to install the update, they provide a workaround:

To work around this issue, use a nested loops join or a hash join by using the Option command instead of the merge join.