To complement, this only seems to happen with regular users. Querying the same datasource with a sysadmin user results in ok queries. Still in our processes we have regular users, and can't always have powerusers to do certain work.

Its strange 1st row is returned, then 99 ommited:

SELECT * FROM OPENQUERY(linkedServerName, '

SELECT level FROM dual CONNECT BY LEVEL < 110 '

)

Thanks a lot to Geoff for his suggestion.

Since we know 1st record is retrieved and then 99 omited, so generate and request 99+ records (cross join to maintain columns for the union in 2nd step) and tag with 1,

then union that with all records and tag with 2,

and finally select all tagged with 2.

I had an issue with the brackets had to use curly brackets. So with a tweak, it worked:

SELECT *

FROM OPENQUERY(linkedServerName,

'select * from

(select a.n, b.* from

(SELECT 1 n FROM dual CONNECT BY LEVEL < 110 ) a

cross join

( select * from OracleTableOrView where rownum = 1 ) b

) c

union all

select 2, d.* from (tf_OracleTableOrView) d

')

AS oracle_table_with_all_records

WHERE (N = 2)

Cheers

P