ODBC to Oracle 64bit missing rows

[Morgan]

Hello all. I'm having a problem that I'm hoping somebody can help with!

I am on SQL Server 2005, 64bit. I am trying to connect to an Oracle server,
setting it up as a linked server. First I tried the Oracle OLE DB drivers,
but certain queries would blow up and bring the whole SQL service down! We'd
get errors like, "unexpected catastrophic error", which would require me to
restart SQL Server to fix.

I talked to a guy from Microsoft at the last PASS conference and he
suggested I use Oracle's 64-bit ODBC driver and MSDASQL to get to it......

So, that's all set up, but my queries are always missing 99 rows! I've
searched on google and have found others in the same situation but no
solutions! Any ideas?

Here's an example... The first one should return 199 like the rest do, but
it does not....
select count(*) from openquery(testodbc, 'select * from OracleTable where
rownum < 200')
--100
select * from openquery(testodbc, 'select count(*) from OracleTable where
rownum < 200')
--199
select count(*) from openquery(testoledb, 'select * from OracleTable where
rownum < 200')
--199
select * from openquery(testoledb, 'select count(*) from OracleTable where
rownum < 200')
--199

any help would be greatly appreciated! Thanks!

[Tch.@discussions.microsoft.com Andrey Tch.]

Morgan,

After painful investigation why we missing some of the data, we just
discovered exactly the same problem! Oracle 10 (10.2.?) ODBC drivers on
Win2008 64bit machine. We weren't able to make Oracle 11 drivers working for
some other reasons. Less of downgrading whole MSSQL cluster to 32 bit , we
don't know what to do. How did you fix your problem?

Thank you very much,
Andrey

[Morgan]

Unfortunately, I never was never able to solve the ODBC missing rows problem.
You may want to try opening up a ticket with Microsoft - I did not do that,
I just tried to figure it out on my own to no avail.

I went back to using Oracle's OLE DB drivers. It makes me nervous to do so
since I've gotten those "Unexpected Catastrophic Error"s requiring me to
restart SQL in the past. But, the set of Oracle queries that I'm running
through the linked server right now haven't given me that error.... One
time, I modified an Oracle query that I was running in an OPENQUERY, and
commented out a few of the last columns. This gave me the "unexpected
catastrophic error" when I ran it, so I deleted the commented out columns &
re-ran it and it's been fine.

So, while this makes me nervous, it does seem like these catastrophic errors
only happen under a certain set of circumstances (which I haven't been able
to pinpoint), and not randomly, and my current queries seem to be OK.

So, I'm trucking on with 64-bit & Oracle's OLE DB drivers.

I am tempted to re-do the server in 32-bit - it would probably be safer, and
may be the smart thing to do, but I just haven't been able to bring myself to
do it yet....

I haven't had the best of luck with 64-bit SQL Servers - seems there's
always something off about them. I know they're *supposed* to be the way to
go, but I'm not convinced!