October 4, 2004 at 12:58 pm
Thank you in advance for any advice!
I am using our SQL Server 2000 to connect to an Oracle 9i database to retrieve/query fields in a set of predetermined views. I have set up the specific Oracle 9i catalog as a linked server. I am able to successfully (for the most part) query the server it, but I am very disappointed in the overall performance. I have tried 3 different providers (types) and was wondering which should work best: (note: I have installed the minimum necessary components of the Oracle 9i client)
Microsoft OLE DB Provider for Oracle
Microsoft OLE DB Provider for ODBC Providers (with a DSN using an Oracle Provider)
Oracle Provider for OLE DB
Each have ‘Allow InProcess’ and ‘Non transacted Updates’ checked in the provider options.
I have had success connecting with each of the three, but have problems querying with the OLE DB for ODBC provider (OLE/DB provider returned an invalid column definition.)
My assumption is that the Oracle Provider of OLD DB should be best?
Or, am I missing another, more performance savvy of querying Oracle from my SQL server?
Thank you very much for you time,
-Timothy Strunk
October 4, 2004 at 2:24 pm
Of the three, the one that worked out best for us is 'Microsoft OLE DB Provider for Oracle' . We tried in vein to use Oracle Provider for OLE DB, however, we ran into issues.
October 5, 2004 at 1:05 pm
Hi
Hildevan O Bezerra
October 5, 2004 at 1:08 pm
Hi
Try using native Oracle OLEDB provider.
We had the same problem here and tried many solution like Microsoft driver and ado connections.
The best way was use the native oracle OLEDB provider.
But , have in you mind , that solution work fine if you runnig SP from oracle and expect result set and/or return from oracle
Hildevan O Bezerra
October 20, 2004 at 4:54 pm
Timothy,
Here are the things we're doing to get the best performance from our Oracle 9 linked servers:
* use the Oracle OLE DB provider
* update the server's registry per KB article 280106 (you'll need to adjust the names for Oracle 9)
* Check 'Allow InProcess' when setting up the linked server
* Use OpenQuery vs. four part syntax (linkserver.catalog.schema.object). As a pass-through, OpenQuery is generally much faster.
I hope this helps.
Mark
November 22, 2004 at 1:52 pm
About OpenQuery, what if you have problems Updating, Inserting and Deleting using OpenQuery?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply