• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • Last Modified:

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.

Thanks
0
Narusegawa
Asked:
Narusegawa
  • 5
  • 4
1 Solution
 
brad2575Commented:
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).

0
 
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.
0
 
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.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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.
0
 
NarusegawaAuthor Commented:
I've just tried using another tool... Query Tool (Using ADO) from http://gpoulose.home.att.net/

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 9.2.0.8.0
0
 
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.
0
 
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') ,
                              to_char(sysdate,'RRRR')-1),'DD-MM-YYYY')

fixed the problem. I have no idea how Oracle had let him create a view with a bad where clause in it.
0
 
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.
0
 
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!
0
 
NarusegawaAuthor Commented:
Just a thank you for your assistance on this.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now