we have a linked server between a SQL Server 2008 R2 instance and an Oracle instance using OraOLEDB.Oracle Provider.

The enabled options for provider are:

  • Dynamic parameter
  • Nested queries
  • Allow inprocess
  • Supports "Like" Operator

The options of the linked server are:

@optname=N'collation compatible', @optvalue=N'false'
@optname=N'data access', @optvalue=N'true'
@optname=N'dist', @optvalue=N'false'
@optname=N'pub', @optvalue=N'false'
@optname=N'rpc', @optvalue=N'true'
@optname=N'rpc out', @optvalue=N'true'
@optname=N'sub', @optvalue=N'false'
@optname=N'connect timeout', @optvalue=N'0'
@optname=N'collation name', @optvalue=null
@optname=N'lazy schema validation', @optvalue=N'false'
@optname=N'query timeout', @optvalue=N'0'
@optname=N'use remote collation', @optvalue=N'true'
@optname=N'remote proc transaction promotion', @optvalue=N'true'

The processes using this linked server just take orders from Oracle, do what it has to do and then sends back to Oracle informations about what have been done.

According to the end users, it seems to be slow.

How can I ...

  1. Assess that the configuration is done appropriately?
  2. How can we make sure that performance is good ? Monitoring queries that goes through it, etc.
  • What are the actual queries you're running? You may find using OPENQUERY a better option if you're not already. – dwjv Jan 12 '16 at 10:57
  • It's an external supplier (the one who built the software we baught) who writes and manage the code for this. They used [LINKED]..SCHEMA.TABLE in the definition of a cursor after which there is a loop with fetches. – Jefferson B. Elias Jan 12 '16 at 11:15
  • This question on StackOverflow may help. – Slogmeister Extraordinaire Mar 21 '18 at 15:48

You must change the Incompatable Database Engine Server Collation into Linked Server Options. Set Value to True

  • 1
    Any chance of getting a little more commentary here to help the next folks who come along? – jcolebrand Jan 30 '17 at 22:49

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.