Linked Server Query Returns Incomplete Results

350 pts.
Tags:
Linked servers
ODBC
OLE DB
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')
ASKED: October 24, 2008  12:42 PM
UPDATED: January 6, 2011  1:36 AM

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

select count(*) from OPENQUERY([TEST],’SELECT * FROM APICKEL.ROOMBED’)

I get 766.

If I run

select * from OPENQUERY([TEST],’SELECT count(*) FROM APICKEL.ROOMBED’)

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: 5  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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • 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:
    report
  • Darryn
    I found this link for the problem with Sage, but it is probably the same for Oracle: http://dorsetsoftware.blogspot.com/2008/06/linked-server-sage-line-50-v-14.html See figure 3 for setting the Linked Server Provider property for "Level zero only".
    765 pointsBadges:
    report
  • 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:
    report
  • 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:
    @provider=N'MSDASQL' 
    ) Sometimes it has taken a while to figure out the issue, because queries which return only one row work correctly.
    10 pointsBadges:
    report
  • Apickel
    [...] Upon a fair amount of head scratching, we discovered that there is a bug in the ODBC driver to Oracle.  See this article:  http://itknowledgeexchange.techtarget.com/itanswers/linked-server-query-returns-incomplete-results... [...]
    0 pointsBadges:
    report

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following