Linked Server Query Returns Incomplete Results

350 pts.
Linked servers
Oracle Database
I have set up a linked server using Microsoft OLE DB Provider for ODBC Driver to an Oracle DB ODBC DSN. The Oracle table I'm querying has 865 rows but when I try to fetch all the rows I only get back 766 rows. Why am I not getting the complete results? Here is the query I'm using: select * from OPENQUERY([TEST],'SELECT * FROM APICKEL.ROOMBED')

Answer Wiki

Thanks. We'll let you know when a new response is added.

That sounds strange.
Have you connected directly to the Oracle database with the same user configured for the ODBC connection, and run the same query ?
The only thing that comes to my mind is the possibility of more than one table with the same name, created on different schemas. You might be querying the wrong table, because of the user configured in the ODBC connection, or the lack of a table synonym.

Yes it is strange, but no – I’m using the right schema and yes I have run a query using just the ODBC
driver and it did return the complete result set. I tested this same problem with another table that
had 980 rows and it also did not return the complete result set.

My next step is to turn tracing on for the ODBC driver. FYI, this is SQL Server 2000 to an Oracle 10g
database. And yes, I’ve installed the latest ODBC driver from Oracle.

Ok, unfortunately I’m not sure how to interpret the trace but I’m working on it. Another FYI, if I run


I get 766.

If I run


I get 865.0 (which is the correct number of rows in the table).

I tried creating views with “rownum <501” and “rownum <766” and on the oracle side the views were
500 and 765 respectively, but when I queried the view from the linked server I got 401 and 666
respectively. Hmmmmmmm….


Discuss This Question: 6  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Darryn
    I had exactly the same problem using a SQL Server linked server to Sage. I also used MS Access to test the ODBC link, and that returned the correct data, so I surmised that it must be an SQL Server bug. I never got to the bottom of it, but if you have SQL Server support, I would raise it with them.
    765 pointsBadges:
  • Darryn
    I found this link for the problem with Sage, but it is probably the same for Oracle: See figure 3 for setting the Linked Server Provider property for "Level zero only".
    765 pointsBadges:
  • Apickel
    Ok - I've got it working now. I installed the latest oracle client and associated libraries/drivers and everything is working as it should.
    350 pointsBadges:
  • Loaf
    This issue has come up a couple times for us, linking from SQL Server 2005 (64-bit) to Oracle 10g. The answer has been to use the "Oracle Provider for OLE DB" as the Provider when creating the Linked Server. The command looks like this:
    EXEC master.dbo.sp_addlinkedserver @server = N'DWDB', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle'', @datasrc=N'DWDEVDB'
    When the provider has been set to "Microsoft OLE DB Provider for ODBC Drivers", that's when we see the issue with row number 2-100 disappearing. (In that case, the command-line provider shows as:
    ) Sometimes it has taken a while to figure out the issue, because queries which return only one row work correctly.
    10 pointsBadges:
  • Marty Zigman Speaks on "Warning: SQL Server Linked Server to NetSuite Issues"
    [...] Upon a fair amount of head scratching, we discovered that there is a bug in the ODBC driver to Oracle.  See this article: [...]
    0 pointsBadges:
  • bhadauria
    Check the coalition of the Oracle database. we were also facing the smae issue but a DBA from oracle rectified it by changing the coalition. Now I am facing the same problem with SQL Server and sql express. When ever I execute a Query the resultant dataset is different (never complete). I was able to recreate the scenario on my local machine by Executing the data Transfer (which normally takes 25 Seconds) after 5 seconds in execution remove the network cable and connect it back after a few seconds. You will not get any error and the process will complete successfully (SQL Server thinks so) but the number of records transferred will be different each time
    10 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: