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
14  12560 
Sudhesh Nayak wrote: 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
Why not use an Oracle database link for real-time access to SQL Server?
Also look at Oracle's SQL Server Transparent Gateway for accessing SQL
Server with PL/SQL.
--
Daniel Morgan http://www.outreach.washington.edu/e...ad/oad_crs.asp http://www.outreach.washington.edu/e...oa/aoa_crs.asp da******@x.wash ington.edu
(replace 'x' with a 'u' to reply)
I need to go from SQL to Oracle as the Oracle db is owned by a
different group within the company... We have more (better) access to
the SQL box...
Thanks for the suggestion though
Sudhesh
Daniel Morgan <da******@x.was hington.edu> wrote in message news:<107272337 1.798845@yasure >... Sudhesh Nayak wrote:
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
Why not use an Oracle database link for real-time access to SQL Server?
Also look at Oracle's SQL Server Transparent Gateway for accessing SQL
Server with PL/SQL.
Can Oracle 8 act as an OLE DB provider to SQL Server as consumer?
In this case there is really no difference between using SQL Server as a
base through OLE DB vs. Oracle through transparent gateway, other than
that of control.
Just my two cents
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Serge Rielau wrote: Can Oracle 8 act as an OLE DB provider to SQL Server as consumer?
In this case there is really no difference between using SQL Server as a
base through OLE DB vs. Oracle through transparent gateway, other than
that of control.
Just my two cents
Serge
Difference is that with the Transparent Gateway and Oracle Developer
could issue PL/SQL against the SQL Server database rather than Transact
SQL. Cuts down on a lot of training and makes it possible to write
single SQL statements that simultaneously hit both back-ends.
--
Daniel Morgan http://www.outreach.washington.edu/e...ad/oad_crs.asp http://www.outreach.washington.edu/e...oa/aoa_crs.asp da******@x.wash ington.edu
(replace 'x' with a 'u' to reply)
One thing you have to watch out for, I don't think Oracle 8.1 supports
the SQL-92 join syntax, IOW you have to do the joins in the where
clause.
Another developer at my company is using DTS and I am not impressed
with the results. I prefer to code whatever ETL I want to do myself,
that way I have a lot more control and much better oversight... when
you use DTS if you want to see what it's doing you have to open up the
packages and click on all the little icons etc, and I don't think
there's any way to step through to debug either.
On 29 Dec 2003 09:44:22 -0800, Su*****@mail.co m (Sudhesh Nayak) wrote: 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
Daniel,
It seems the original poster is sitting on SQL Server. The Oracle DBMS
belongs to "another Group". So the argumnet of traing goes teh other way
around.
I don't know SQL Server well enough, but I'd be surprised if they don't
support OLE Table Functions which then, of course, allows T-SQL to hit
multiple sources at the same time, just like transparent gateway does.
The really interesting question is: How much optimization does
transparent gateway provide (in Oracle 8)? Only if it supports
distributed optimization with subquery pushdown to SQL Server will there
be any conceptual difference since OLE does not provide such
capabilities. Judging by the whitepapers available so far it seems some
of this heterogeneous optimization is coming in Oracle 10g, but that
isn't what the poster has.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Serge Rielau wrote: Daniel,
It seems the original poster is sitting on SQL Server. The Oracle DBMS
belongs to "another Group". So the argumnet of traing goes teh other way
around.
I don't know SQL Server well enough, but I'd be surprised if they don't
support OLE Table Functions which then, of course, allows T-SQL to hit
multiple sources at the same time, just like transparent gateway does.
The really interesting question is: How much optimization does
transparent gateway provide (in Oracle 8)? Only if it supports
distributed optimization with subquery pushdown to SQL Server will there
be any conceptual difference since OLE does not provide such
capabilities. Judging by the whitepapers available so far it seems some
of this heterogeneous optimization is coming in Oracle 10g, but that
isn't what the poster has.
Cheers
Serge
I'm not sure why you specify Oracle 8 as is it over a decade old. Could
be 8i, could be 9i, and it is essential the OP find out. If the SQL must
be executed on the SQL Server side, as has since been indiated,
Transparent Gateway is out. My suggestion is that the OP work out the
solution with the Oracle DBA responsible for the database. It is likely
they will not say "Yes" to whatever is proposed but will have very
specific ideas and concerns.
You are correct about 10g. I've been working with it for months and it
is quite a different animal.
--
Daniel Morgan http://www.outreach.washington.edu/e...ad/oad_crs.asp http://www.outreach.washington.edu/e...oa/aoa_crs.asp da******@x.wash ington.edu
(replace 'x' with a 'u' to reply)
Here is how the thread started....
"I have an Oracle (8.1) & a SQL Server 2000 database with Production data."
Larry won't give me Oracle 10g early I'm affraid. But that's OK :-)
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Sudhesh Nayak |
last post by:
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...
|
by: David Gray |
last post by:
Hello all,
Having problems connecting to an Oracle 9i database from within
SQL/Server 2000 using the Security/Linked Servers feature.
Server1 (SQL/Server)
-----------
Windows Server 2003, Standard edition
MS SQL/Server 2000
Oracle 9i Client kit (OLEDB & ODBC) & Enterprise management tools
|
by: Pardhasaradhy |
last post by:
Hello,
I have a linked server to oracle 7.1 from SQL 2000. when I try to
execute simple select statement which returns one row of data using
openquery is not fetching the data. After 30 minutes SQL Query
analyser is hanging. This is happening on the production server and it
is working from last 6 months. I have tried by deleting and...
|
by: Ellen K |
last post by:
Hi all,
I set up our Oracle Financials as a linked server to one of my SQL
Server boxes. On running a test query, I got the following error
message:
OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column.
Metadata information was changed at execution time.
OLE DB error trace .
The Oracle datatype of the column with the...
|
by: tim.pascoe |
last post by:
I'm currently trying to establish a linked server to an Oracle
database.
Setup:
Connecting to 8x version of Oracle
Using 9i client tools (Net Manager)
SQL-Server 2000
Windows 2000
I installed the Oracle 9i client tools, and set up a Service for the
| | |
by: arichie |
last post by:
Can anyone point me where can I find a good architectural document on
how the Linked server works in SQL Server. I am not asking about how to
use Linked server, I am interested in know how it is defined and
architected.
Our project needs a data transfer from SQL server database to the
Oracle database, I am trying to find out whether can we...
|
by: Joe |
last post by:
I am using Access 2003 and are linking to an Oracle 9i ODBC datasource
(using Oracle ODBC drivers). After linking the tables in Access, I inspect
the data contained in the linked tables. For tables that involve a number
field as the primary key, the data is returned successfully. For tables
that involve a character field (e.g. CHAR(3) or...
|
by: Chris |
last post by:
I need to link some data from SQL Server 2005 with Oracle 10 data.
One way is to link Oracle server to SQL Server and use ROWSOURCE for
retrieving data.
What other ways for joing data from both databases exist ?
Can I do it from SQLCRL VB - Stored Procedure ? If yes, what objects
shall I use for opening database and running an sql ?
|
by: aberton |
last post by:
Hi all,
I am having major performance issues between my linked server and an Oracle10 db. I have created a number of views in my SQL Server database which map to corresponding Oracle tables via a linked server.
In my Oracle environment via Sql*Plus I run a simple query (count) which does a join between a 400K table and a 2.5 mil table - this...
|
by: aberton |
last post by:
Hi all,
I am having major performance issues between my linked server (SQL Server) and an Oracle10 db. I have created a number of views in my SQL Server database which map to corresponding Oracle tables via a linked server.
In my Oracle environment via Sql*Plus I run a simple query (count) which does a join between a 400K table and a 2.5 mil...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
| | |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |
