Microsoft Linked Servers

Hi, I'm select from a view in an oracle database using SQL Server 2005's openquery method.

select * from openquery(mylink,'select * from myview where reference = ''12124''')

mylink being a link to an Oracle 9i database. But using an 11i driver (as i'm doing this on a 64bit server)

The above returns 11 records. Which while the data appears correct, is out of date!
Using the same login details as the mylink, with sqlplus from the command line and the same sql (without the openquery) I get 5 records which is correct.

Any idea's at all?

I've restarted sql server already just incase it was caching result sets but now I'm at a loss. I'm awaiting an admin to restart Oracle, but would like any other ideas too.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

do you have multiple oracle databases with the same tables (like a DEV/PROD instance)?

If so could the linked server be pointing to the wrong server?  OR the DSN (if that is what you are using when the linked server was created) could be pointing to the wrong server.

SQL should not be the cause of the issue of pulling back bad/out of date data.  If anything it could be an oracle caching issue (like you described).

johnsoneSenior Oracle DBACommented:
Oracle does not cache query results.  It would cache the data blocks to run the query, making it faster, but it would not cache the results.

If data was update in a separate Oracle session and not committed (or it was committed after the transaction started), your query would not see the updated data, it would see the previous version.
NarusegawaAuthor Commented:
There is only one entry in my tnsnames.ora file on this machine. And both connections used the same usersname and password to login to it. I considered the commit issue, but then I tried the following:

I restarted the MSSQL 2005 SQL Service to clear out any memory it was using and connections too. I created a new linked server to the same instance, just with a slightly different name.

Using either of the Linked Server connections told me 11 records.

Yet I can open a sqlplus.exe connection t othe same instance that the linked servers are using, with the same username and password too. My count comes back with 5 records.

I'm really struggling to conceive how any database could supply (in a view) out of date data, unless you actually had gone through loads of hoops to make it do so.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

johnsoneSenior Oracle DBACommented:
Could it be something on the SQL Server side?  Maybe SQL Server is not sending the where clause across the link and trying to evaluate it itself.  I think that is a stretch though.
NarusegawaAuthor Commented:
I've just tried using another tool... Query Tool (Using ADO) from

This let's me specify the OleDB driver to use, and this also has the same problem.

We've tried recreating the view under a different name, tried dropping and creating it. Still the same problem. I'm suspecting now if it's a problem with the OraOLEDB.Oracle driver problem from 11.1.0 talking with a server on version
johnsoneSenior Oracle DBACommented:
That is certainly a possiblity.

Try the Oracle 10g instant client.  That has an ODBC component and is certified to work with 9i.
NarusegawaAuthor Commented:
Having looked at this now for so long, I demanded that the Oracle admin give me the view definition so that I could run the select against the tables directly and bypass the view.

I found the following in the where clause

and nvl(a.end_date,sysdate) > to_date('01-AUG'||decode(sign(to_number(to_char(sysdate,'mm')) -8), 1,
                              to_char(sysdate,'RRRR') ,
                              to_char(sysdate,'RRRR')-1)      )

simpy changing this to

and nvl(a.end_date,sysdate) > to_date('01-AUG-'||decode(sign(to_number(to_char(sysdate,'mm')) -8), 1,
                              to_char(sysdate,'RRRR') ,

fixed the problem. I have no idea how Oracle had let him create a view with a bad where clause in it.
johnsoneSenior Oracle DBACommented:
It is not a bad where clause.  It is bad practice.

Whoever created the view depended on a default date format, which is never good, the date format should always be explicitily state.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NarusegawaAuthor Commented:
It still doesn't explain why using a program called TOAD (apparently that's what he uses) and SQLPLUS gave back different results than that given by OraOLEDB.Oracle OleDB driver.

Unless, the different drivers gave different default date formats but I didn't even think you could specify that at driver level.

I tried to run without explicit date formats but it complained of missing arguments. My only conclusion is that OleDB Drivers are a lot more strict. I will give you the points for the answer to this for your assistance on helping. Thank you!
NarusegawaAuthor Commented:
Just a thank you for your assistance on this.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.