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