Re: Connecting SQL Server from Oracle

From: Darrell Landrum <darrell_at_landrum.com>
Date: Sun, 25 Jul 2004 23:52:40 -0500
Message-ID: <001601c472cc$61672120$6401a8c0@otherrealm>

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_at_hotmail.com> To: <Oracle-L_at_freelists.org> 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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Sun Jul 25 2004 - 23:49:13 CDT