Re: Oracle Linked Servers
From: Michael D. Long (michael.d.long-nospam_at_comcast.net)
Date: 09/02/04
- Next message: light_wt: "Index question"
- Previous message: Shrikant Patil: "creaet cluster index will rebuilt all non-clustered indexes in DB?"
- In reply to: Craig: "Oracle Linked Servers"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 1 Sep 2004 23:12:21 -0400
To use distributed transactions with the Oracle OLE DB Provider in your
environment you'll need to install the Oracle Service for MTS.
-- Michael D. Long Microsoft MVP - Windows SDK "Craig" <anonymous@microsoft.com> wrote in message news:e4p3OsDkEHA.3968@TK2MSFTNGP11.phx.gbl... > I've got an Oralce 8i db that I'm running as a Linked Server inside SQL > Server 2000. Linking seems to work well. Then I fire off a few queries via > Query Analyzer at a table with ~3000 rows of data, and get the following > responses . . . > > select top 1000 * from table - executes in 1 second > select top 2000 * from table - executes in 1 second > select top 3000 * from table - executes in 1 second > > select top 4000 * from table - runs for > 5 minutes > select * from table - runs for > 5 minutes > > Some additional comments on my setup - > > 1. I created a blank database in sql server > > 2. I created a set of views in sql server, one view for each table in > oracle, with a "select * from [oracle table]" for each view (acutally used > correct syntax to pull oracle data - above select is hypothetical ;-) > > 3. I have a few reporting views in sql server that aggregate base views; but > that is the extent of view nesting, 2 deep > > Any comments? I was originally wanting to write things off to RAM, but I'm > wondering if there are issues with the ODBC / translation of the termination > of data. I'm using the Microsoft OLE DB Provider for Oracle to link the > databases. When I try the Oracle OLE DB Provider, the tables show up in the > linked server but any Query Analyzer queries agains them error out with the > following: > > Server: Msg 7320, Level 16, State 2, Line 2 > Could not execute query against OLE DB provider 'OraOLEDB.Oracle'. > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Execute > returned 0x80040155]. > > Thanks for any help. > > -craig > >
- Next message: light_wt: "Index question"
- Previous message: Shrikant Patil: "creaet cluster index will rebuilt all non-clustered indexes in DB?"
- In reply to: Craig: "Oracle Linked Servers"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
