Re: Connecting SQL Server from Oracle
- From: "Darrell Landrum" <darrell@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 25 Jul 2004 23:52:40 -0500
Jagadish,
I've written a step by step document for this for use in my office.
Unfortunately it is there where I can't access it right now (on vac.).
However, I think there was an Oracle-L thread about that a while back. A
search for tg4msql might point you that direction.
All in all, the concept is pretty simple. What you want to do is install
Tranparent Gateway for Sql Server from the Oracle Server CD on your Sql
Server host (It doesn't have to be on the same machine, but is easier to
configure that way).
----
Licensing disclaimers:
1) I'm pretty sure this will count as a client against your Sql Server, so
if your licensed "per seat", take this into consideration.
2) I don't know if these heterogeneous services (transparent gateway) are
licensed seperately, so another thing to find out before using.
----
Basically, once installed, you'll configure a listener for your gateway
services that will "emulate" an oracle instance, by listening for connection
requests like a regular oracle listener only passing it to the Sql Server it
is configured to talk to. The big, big difference is the only way to use
this connection is from a database link. You can't talk to it from sqlplus
or other clients.
So, then you'll need to configure your tnsnames.ora, or other connection
method, on the oracle side and create a database link in that oracle
instance.
There is a document you should read (on metalink):
How to Setup TG4MSQL (Transparent Gateway for MS SQL Server) 231458.1
and on otn.oracle.com:
http://otn.oracle.com/pls/db92/db92.show_toc?partno=a96544&remark=drilldown&word=tg4msql
Now, all that said, if you're just wanting to do simple data transfers
between the 2 systems, perhaps like a snapshot, driving this from the Sql
Server side is much easier. My recommendation there is to:
1) Install the Oracle client software on the Sql Server host. Configure
tnsnames.ora, etc.
2) Use dts packages to push or pull data. (And when creating dts packages,
use Microsoft's OLE DB driver for Oracle, it seems to be the most stable.
This is as apposed to the ODBC driver from either vendor.)
So, if it is simple data copy transfers dts is one way, but if you need to
use complex sql or even pl/sql, you might need the gateway because
procedural programming is 1000% more robust from the Oracle side.
Good luck!
----- Original Message -----
From: "Jagadish Chatarji" <jag_chat@xxxxxxxxxxx>
To: <Oracle-L@xxxxxxxxxxxxx>
Sent: Sunday, July 25, 2004 8:20 AM
Subject: Connecting SQL Server from Oracle
> May I know how to connect SQL Server 2000 from Oracle..in a step by step
> manner..any link is also appreciated..thank you
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
