473,549 Members | 2,698 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Linked Server to Oracle

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
Jul 20 '05 #1
14 12548
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)

Jul 20 '05 #2
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.

Jul 20 '05 #3
Sudhesh Nayak wrote:
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


Then I'd suggest you talk to the Oracle group. They will likely not
allow you to access their database by whatever means you choose but
their DBAs will likely have strong conditions they will want to place
you your activities: I know I would.

--
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)

Jul 20 '05 #4
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

Jul 20 '05 #5
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)

Jul 20 '05 #6
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


Jul 20 '05 #7
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

Jul 20 '05 #8
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)

Jul 20 '05 #9
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

Jul 20 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
4500
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...
3
24267
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
2
3862
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...
11
13248
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...
2
4981
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
4
2449
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...
7
11820
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...
3
4160
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 ?
0
1261
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...
0
1543
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...
0
7524
marktang
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...
0
7720
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7960
jinu1996
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...
1
7475
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...
0
5089
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3501
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...
0
3483
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1944
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
766
bsmnconsultancy
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.