My customer somehow only has SQL Server 2005 instance. It has linked server to oracle database. I've noticed that sql server doesn't send any filters to oracle side and apply them only after it retrieves all data from oracle. Which is quite opposite to work of sql server - sql server linked server.

I'm wondering - could it be because of sql server version? Maybe 2005 is to old for that?

For example:

SELECT * FROM OPENQUERY (ORCLLS, 'Select * from ADMIN.IE_Docs_Subscrs where F_DRSO = ''84c9e2c5-2634-4f6a-b1b7-e1505d4a1c39''')

Result: 0 seconds. 24 rows

SELECT * FROM OPENQUERY (ORCLLS, 'Select * from ADMIN.IE_Docs_Subscrs')
WHERE  F_DRSO = '84c9e2c5-2634-4f6a-b1b7-e1505d4a1c39'

Result: 1m 33 seconds. 24 rows


Got it. It's because of OPENQUERY. But how can I turn it into four-part name?

| improve this question | | | | |

By default SQL Server doesn't believe that string comparisons will have the same semantics on the linked server that they have locally. So it can't "push the predicate" to the remote server.

In particular Oracle is always case-sensitive, and SQL Server by default is not. So in SQL Server you get the same results with

WHERE  F_DRSO = '84c9e2c5-2634-4f6a-b1b7-e1505d4a1c39'


WHERE  F_DRSO = '84C9e2E5-2634-4f6A-B1B7-E1505D4A1C39'

But not in Oracle.

You can set the "Collation Compatible" setting of the linked server to allow SQL Server to push predicates.

But I really prefer to use OPENQUERY or EXEC ('sql') AT LinkedServer and retain explicit control over the remote query.

To enable the predicate to be sent to the linked server, the query needs to use 4-part names.

For oracle this would look something like:

select * 
from ORCLLS..ADMIN.IE_Docs_Subscrs
WHERE F_DRSO = '84c9e2c5-2634-4f6a-b1b7-e1505d4a1c39'

Oracle traditionally (pre 12) has only a single database per instance, so the second part of the 4-part name is empty.

| improve this answer | | | | |
  • I always wondered why sp_executesql couldn't also use the AT syntax too. – Erik Darling Dec 16 '17 at 15:09
  • I've set Collation Compatible setting and didn't notice any changes concerning performance. SQL server still pulls all data and filters them locally. – nikita Dec 18 '17 at 13:46
  • At a minimum you would need to use a 4-part name. I updated my answer with an example. – David Browne - Microsoft Dec 18 '17 at 13:53
  • Ok thanks. My another question would be how to set up 4-part name. The only provider that managed to connect to Oracle on customer's machine is MSDAORA that seems to not supporting 4-part names – nikita Dec 18 '17 at 16:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.