Re: Linked Server to Oracle
Date: Mon, 02 Feb 2004 21:22:32 GMT
Message-ID: <dset109u84kjgq7fuldfk7olvs9mqchlt4_at_4ax.com>
On Sun, 1 Feb 2004 19:35:10 +0000 (UTC), "David Penney"
<anon_at_noone.com> wrote:
>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 Mon Feb 02 2004 - 22:22:32 CET