Re: Linked Server to Oracle

Lyndon
thats one way to go - MetaMatrix does it a faster way without having the overhead to write then read the data into the second database to do a join. ts also quicker to deliver because the operatioin is specified in a graphical modeling tool. Its also quicker to maintain than the soluution you found. The product also transforms the data into a diffeerent datamodel & can do reads & writes. Effectively the two databases are federated into a Virtual Database with its own specific schema.

However if your solution is fast enough & a single limited case seems a good solution for you, for more complex requirements its not optimal.

Regards,

             Davd

"Lyndon Hills" <lyndon_at_nospam.tenegi.com> wrote in message news:dset109u84kjgq7fuldfk7olvs9mqchlt4_at_4ax.com...
> On Sun, 1 Feb 2004 19:35:10 +0000 (UTC), "David Penney"
> <anon_at_noone.com> wrote:
>
> >Sudhesh,
> >my company has a product, MetaMatrix, that will link the two in exactly
 the
> >manner you require. It will also combine the Oracle & MS SQLServer
 schemas &
> >as a unified virtual database make them both together look like an
 instance
> >of your MSSQLServer database - then you can get all data together.
> >
> >Regards
> > David Penney
> > http://www.metamatrix.com
> >
> >"Sudhesh Nayak" <Sudhesh_at_mail.com> wrote in message
> >news:8d85e256.0312290944.26fc9878_at_posting.google.com...
> >> Hi,
> >> I have an Oracle (8.1) & a SQL Server 2000 database with
> >> Production data. There are situations when I need data from both the
> >> databases. My first choice was to link Oracle to SQL and run DTS
> >> overnight. But this would have a 1 day latency not to mention the time
> >> it would take.
> >>
> >> 1. Has any one tried real time access via Linked server to Oracle?
> >> How good is the performance?
> >>
> >> 2. The Oracle db is fairly big, so I'm kinda not in favor of
> >> copying the whole thing over into SQL overnight. Is there an easier
> >> way to just get only the changed records from Oracle?
> >>
> >> 3. Is there a better solution to this?
> >>
> >> 4. Lastly, if I use OPENROWSET or OPENQUERY, can I dynamically
> >> change the SQL that you pass it? e.g. can the query accept a
> >> parameter?
> >>
> >> Thanks in advance
> >> Sudhesh
> >
> I'm doings this. The main problem I had was wanting to relate data in
> Oracle to that in SQL Server. It was horribly slow and we established
> that what was happening was that MSSQL was going to pull the whole
> table over from Oracle and then execute the query.
>
> I solved using openquery. I created tables in SQLServer to hold what I
> wanted from Oracle, populate using openquery and then deleted it.
> Openquery performance is fine. You can use parameters, but you do it
> by building up a string, containing the openquery statement and the
> actual query and then you exec the string. This involves a nice game
> of getting the right number of quotes!
>
> A couple of other gotchas;
>
> The oracle table names and columns have to be in capitals.
> The oledb driver doesn't like columns defined as number, it wants them
> to be NUMBER(12,0) or whatever. (The error is that the schema has
> changed between parse and execution, or words to that effect).
>
> Depending on your exact setup you may not find these problems, but we
> did.
>
> If you want a sample post a reply, as I'm not at work right now.
Received on Sat Feb 07 2004 - 22:05:18 CET