none
Linked Server using Oracle OLEDB 64-bits Win 2008 R2

    Question

  • Hi,

    I have 2 boxes running SQL Server 2008 and using linked Oracle OLEDB that behaves different for same query.

    On 1st machine, using win 2003 , SQL Server 2008 SP1 on a 64-bit server I  I created a linked server to Oracle database using Oracle OLEDB provider. When I submit query to a Oracle database using Oracle OLEBD using WHERE clause, this WHERE clause is sent to Oracle, then it uses an index to resolve query and send result already filttered to SQL Server.

    I have a 2nd box running Windows 2008 R2, SQL 2008 SP2 on a 64-bit server and queries are working fine, but when I filter some column of a linked server table using WHERE clause, SQL Server sends to Oracle SELECT without where clause, Oracle performs a table scan on that table and then sends results to SQL Server, that applies filter to the result. 

    The problem is that some Oracle tables are huge and sending a SELECT without where clause causes table scans on Oracle, what should be avoided.

    I already tried to change OLEDB properties and also tried to change linked server properties, but none of them solved the problem.

    Does anyone knows what I should change on 2nd server to make it works like 1st server?


    Se a resposta resolveu sua questão ou problema, classifique-a para manter a qualidade do forum e a confiabilidade dos participantes.

    Alex M. Bastos
    http://bastosalex.spaces.live.com
    Wednesday, January 26, 2011 7:14 PM

All replies

  • Alex,

    I too had similar troubles when migrating SS2005 to SS2008R2.   To cut to the chase the link below takes you to a thread from a poster that documents well the problems with getting an OLE DB driver for Oracle running and working in SS2008R2.  One thing to remember after getting the linkserver up is to make sure to set the configuration on the linkserver properties of the link provider (right mouse on the linked server "providers/Oracle OLEDB" then properties) to "allow passthrough".  Forgetting this last bit is frustrating when developers are working from their own workstations/laptops etc...

    http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/thread/da8338c8-308e-4046-bb47-1e9cedc451ee/

    Don't re-invent or duplicate work someone else has already done, thank them for their advice and collaboration and move on....

     

    Tuesday, May 17, 2011 3:43 PM