bad results from linked server to oracle

Using an openquery select statement against a linked server to oracle is
consistently yielding an incomplete result set. Using the Oracle Client, 225
records are returned, yet the SQL Server 2000 linked server returns only 126
records. Both statements were run using the same security credentials. The
linked server is connected to Oracle via odbc and as a test, i ran the same
statement against the same ODBC DSN in Access and did get the correct results.
Therefore the problem does not appear to be with the ODBC driver but with the
linked server itself.
Here is a representation of the tests i have done thus far:
in Sql server i am accessing the linked server using the OPENQUERY statement
as follows:
SELECT *
FROM OPENQUERY(oracledb, 'SELECT *
FROM ORACLETABLE') Rowset_1
running this statement has yielded 126 records
from ms access using the same ODBC system dsn i am getting 225 records with
this statement:SELECT *
FROM ORACLETABLE
and from the SQL Plus Oracle client i am getting 225 records with the
following statement:SELECT *
FROM ORACLETABLE;That is a bit weird. Have you tried using a different table? OPENROWSET
(as a test)? Drop/recreate the LS to Oracle?
Might look here as well:
How to set up and troubleshoot a linked server to Oracle in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;280106
HTH
Jerry
"inteldsn via SQLMonster.com" <u15033@.uwe> wrote in message
news:56172ad97fe9e@.uwe...
> Using an openquery select statement against a linked server to oracle is
> consistently yielding an incomplete result set. Using the Oracle Client,
> 225
> records are returned, yet the SQL Server 2000 linked server returns only
> 126
> records. Both statements were run using the same security credentials. The
> linked server is connected to Oracle via odbc and as a test, i ran the
> same
> statement against the same ODBC DSN in Access and did get the correct
> results.
> Therefore the problem does not appear to be with the ODBC driver but with
> the
> linked server itself.
> Here is a representation of the tests i have done thus far:
> in Sql server i am accessing the linked server using the OPENQUERY
> statement
> as follows:
> SELECT *
> FROM OPENQUERY(oracledb, 'SELECT *
> FROM ORACLETABLE') Rowset_1
> running this statement has yielded 126 records
> from ms access using the same ODBC system dsn i am getting 225 records
> with
> this statement:SELECT *
> FROM ORACLETABLE
> and from the SQL Plus Oracle client i am getting 225 records with the
> following statement:SELECT *
> FROM ORACLETABLE;|||good suggestion. i did look at that msdn article earlier, i have not been
able to check all of the tables yet, but so far this behavior is not on all
of the tables. update from testing: if i run select * from openquery ('select
count(*) from oracletable') recordset_1 I do get the magic number 225.
I have also queried using a where statement for one of the missing records
and the missing record DOES return. so why it does not return using select *
is the mystery.
Jerry Spivey wrote:
>That is a bit weird. Have you tried using a different table? OPENROWSET
>(as a test)? Drop/recreate the LS to Oracle?
>Might look here as well:
>How to set up and troubleshoot a linked server to Oracle in SQL Server
>http://support.microsoft.com/default.aspx?scid=kb;en-us;280106
>HTH
>Jerry|||NULLs on the other end? Might try COUNT(COLUMN) just to see what it
returns. I'd be looking at the column values for a row that doesn't come
back without the WHERE as compared to one that does.
HTH
Jerry
"inteldsn via SQLMonster.com" <u15033@.uwe> wrote in message
news:56175b9b94e48@.uwe...
> good suggestion. i did look at that msdn article earlier, i have not been
> able to check all of the tables yet, but so far this behavior is not on
> all
> of the tables. update from testing: if i run select * from openquery
> ('select
> count(*) from oracletable') recordset_1 I do get the magic number 225.
> I have also queried using a where statement for one of the missing records
> and the missing record DOES return. so why it does not return using select
> *
> is the mystery.
>
> Jerry Spivey wrote:
>>That is a bit weird. Have you tried using a different table? OPENROWSET
>>(as a test)? Drop/recreate the LS to Oracle?
>>Might look here as well:
>>How to set up and troubleshoot a linked server to Oracle in SQL Server
>>http://support.microsoft.com/default.aspx?scid=kb;en-us;280106
>>HTH
>>Jerry|||Jerry,
no nulls on the other end. SELECT *
FROM OPENQUERY(oracledb, 'SELECTcount (*)
FROM ORACLETABLE') Rowset_1 yields a result of 255 just as it should.
if i put a where clause on it, i can get any of the 255 actual records in the
table, but i can't get them all from a general select statement.
Jerry Spivey wrote:
>NULLs on the other end? Might try COUNT(COLUMN) just to see what it
>returns. I'd be looking at the column values for a row that doesn't come
>back without the WHERE as compared to one that does.
>HTH
>Jerry
>> good suggestion. i did look at that msdn article earlier, i have not been
>> able to check all of the tables yet, but so far this behavior is not on
>[quoted text clipped - 19 lines]
>>Jerry|||TJI: What is the possibility that the table on the Oracle side does not have
a unique key and what you are seeing are the unique records?
I have seen that OpenQuery/OLEDB has a preference for unique identifiers.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"inteldsn via SQLMonster.com" wrote:
> Jerry,
> no nulls on the other end. SELECT *
> FROM OPENQUERY(oracledb, 'SELECTcount (*)
> FROM ORACLETABLE') Rowset_1 yields a result of 255 just as it should.
> if i put a where clause on it, i can get any of the 255 actual records in the
> table, but i can't get them all from a general select statement.
>
> Jerry Spivey wrote:
> >NULLs on the other end? Might try COUNT(COLUMN) just to see what it
> >returns. I'd be looking at the column values for a row that doesn't come
> >back without the WHERE as compared to one that does.
> >
> >HTH
> >
> >Jerry
> >> good suggestion. i did look at that msdn article earlier, i have not been
> >> able to check all of the tables yet, but so far this behavior is not on
> >[quoted text clipped - 19 lines]
> >>
> >>Jerry
>|||the results list is missing 99 unique new records, so that is not the answer.
WORKAROUND: MS techs were unable to solve this either, but what we did do was
to abandon using the provider for odbc to create the linked server. Here's
what i have learned so far. using the microsoft provider for odbc driver to
create the linked server, the linked server fails to provide consistently
correct results. I switched to using the provider for ole db for oracle and i
am now getting good results. However, you have to download the provider for
ole db from the oracle site in order to install this and get it working if
your oracle installation is anything more recent than 8.2. there are other
dependencies requiring additional software and configuration if you need to
use distributed transactions as well.
so basically, this is a flaw in the way sql server 2000 handles linked
servers created through the odbc provider.
jrpm wrote:
>TJI: What is the possibility that the table on the Oracle side does not have
>a unique key and what you are seeing are the unique records?
>I have seen that OpenQuery/OLEDB has a preference for unique identifiers.
>> Jerry,
>[quoted text clipped - 17 lines]
>> >>
>> >>Jerry
Message posted via http://www.sqlmonster.com|||UGH!!! I should have asked you that. I had an issue using the ODBC driver
for ORACLE (similar but not the same) at a submarine base consult one time.
I dropped the LS and recreated it using the OLE-DB provider for ORACLE and
it worked fine.
Sorry didn't think of that sooner.
Jerry
"inteldsn via SQLMonster.com" <u15033@.uwe> wrote in message
news:5623fa2fc9ebd@.uwe...
> the results list is missing 99 unique new records, so that is not the
> answer.
>
> WORKAROUND: MS techs were unable to solve this either, but what we did do
> was
> to abandon using the provider for odbc to create the linked server. Here's
> what i have learned so far. using the microsoft provider for odbc driver
> to
> create the linked server, the linked server fails to provide consistently
> correct results. I switched to using the provider for ole db for oracle
> and i
> am now getting good results. However, you have to download the provider
> for
> ole db from the oracle site in order to install this and get it working
> if
> your oracle installation is anything more recent than 8.2. there are other
> dependencies requiring additional software and configuration if you need
> to
> use distributed transactions as well.
> so basically, this is a flaw in the way sql server 2000 handles linked
> servers created through the odbc provider.
> jrpm wrote:
>>TJI: What is the possibility that the table on the Oracle side does not
>>have
>>a unique key and what you are seeing are the unique records?
>>I have seen that OpenQuery/OLEDB has a preference for unique identifiers.
>> Jerry,
>>[quoted text clipped - 17 lines]
>> >>
>> >>Jerry
>
> --
> Message posted via http://www.sqlmonster.com