Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Connecting SQL Server from Oracle

RE: Connecting SQL Server from Oracle

From: Goulet, Dick <>
Date: Mon, 26 Jul 2004 09:59:31 -0400
Message-ID: <>

Gateways from Oracle are licensed separately. $15K per server (Oracle) = if I remember correctly.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-----Original Message-----
From: Darrell Landrum [] Sent: Monday, July 26, 2004 12:53 AM
Subject: Re: Connecting SQL Server from Oracle


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 lldown&word=3Dtg4msql

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!

> May I know how to connect SQL Server 2000 from a step by =
> manner..any link is also appreciated..thank you

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Please see the official ORACLE-L FAQ:
To unsubscribe send email to:
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Mon Jul 26 2004 - 08:56:16 CDT

Original text of this message