Re: Linking MS SQL to Oracle 9i

Unfortunately, we're not running Oracle's App Server or the Java VM. We're simply using database capabilities. However, data we have there relates to other data on MS SQL Server, so we'd like to set up views that join this data. Of course, getting to it seems to be the biggest issue.

From what I can gather, we can either purchase Oracle's Transparent Gateway for MS SQL Server or we need to configure Generic Connectivity and Hetergeneous Services. Since we won't be spending $15k for the former, we'll be using the latter. From there it get's hazier.

From the diagram in the Oracle9i Heterogeneous Connectivity Administrator's Guide (7-1), there is a need for an ODBC driver manager, an ODBC driver, and a non-Oracle system client. Later, in ODBC Connectivity Requirements, it states that for Unix systems (we run Sol 7), a driver manager is not required. It makes no mention of the client.

Further, the commercial ODBC connection solutions I've seen (OpenAccess, easysoft, Connect for ODBC) seem to be bridged solutions which require an ODBC server on the MS SQL Server to go along with the ODBC client-side driver. This is no good for us. Our MS SQL Server people will not go for this.

One possible solution seems to be a combination of open source software. If Generic Connectivity actually does require a driver manager, we can use unixODBC or iODBC. For a client, we can use SQSH.

Whether there is an open source ODBC driver is another question. I can get a TDS client from freetds.org, but is this the same as an ODBC driver? I think it can be configured in this way, and then used with iODBC or unixODBC driver managers but I'm not absolutely certain. And then, could Oracle handle it? Would I still need some sort of non-Oracle system client? If so, would SQSH do?

I have the feeling I'll be doing a lot of experimentation. Can anyone speak to these issues?

TIA, Bill

"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:<DPKb9.47791$kp.694765_at_rwcrnsc52.ops.asp.att.net>...
> If you can find a Java driver that can talk to a SQL Server db then just
> load it into Oracle (via the load Java command) and use it.
> Jim
> "Bill Yager" <Bill.Yager_at_Dirigo.com> wrote in message
> news:e70fc892.0208291119.727e3217_at_posting.google.com...
> > Hi,
> >
> > Was wondering what methods exist for accessing MS SQL data from an
> > Oracle 9i database. I only need READ ONLY access to the MS SQL DB and
> > need a method that doesn't require additional licencing costs (if
> > possible), such as capabilities built-in to 9i. I know I can set up a
> > DB link to another Oracle DB, can I do the same to MS SQL? My 9i DB
> > runs on Solaris 7.
> >
> > Thanks,
> >
> > Bill
Received on Tue Sep 03 2002 - 23:32:21 CEST