different results from same query using different linked servers

[BillLumbergh]

I work on an application that uses MS SQL Server 2K. We have a need to periodically reference a linked server to gather some Oracle data inside SQL Server queries. My linked server knowledge is sketchy at best, and our DBA who manages these connections is not readily available to us.

Anyhow, we have one linked server set up that I am told uses Microsoft drivers to make the connection. Let's call that 'LSM'. We have another linked server set up that I am told uses Oracle drivers to make the connection. Let's call that one 'LSO'. Based on certain requirements that I do not completely understand but that I believe have to do with nesting and transaction handling capabilities, we sometimes have to use LSM and other times have to use LSO. If I run the exact same query using both linked servers, I get different results.

For example, executing:

select columnA
from OPENQUERY
(LSM, 'select columnA from table1 where columnA like ''a%''')

gives me different results than executing:

select columnA
from OPENQUERY
(LSO, 'select columnA from table1 where columnA like ''a%''')

Assuming that both connections reference the same Oracle database, how is this possible? I was thinking maybe one linked server is based on different permissions than the other, but I am not aware that it would possible to give LSO permission to one range of values of columnA in table1 while giving LSM permission to another range of values of columnA in table1, and that is the only way I could think this could happen. Even then, most of the return values overlap, it's just that one connection consistently returns more rows than the other.

Has anyone ever seen this issue? Is it a bug, by design, or something that can be resolved? If so, how did you do it?

Thanks in advance.

 

[Catadmin]

It could be a permissions thing. If the LSM connection object is mapped to an Oracle login that is filtered horizontally (by record) or vertically (by column), or if the LSO one is mapped to a login that is filtered, then it is entirely possible that you're reading two different sets of data.

On the Oracle side, verify the logins both connection objects are mapped to. And verify the permissions too.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!