Re: Oracle Gateway for SQL Server

I haven't used the Transparent Gateway for SQL Server, but I've installed, confgured and used the Transparent Gateway for Sybase. And we all know that SQL Server is very similar to Sybase, right?

My environment was Oracle7 7.3.4 on NT 4.0, Sybase 11 on NT 4.0 and TG4 Sybase on NT 4.0. In fact, they were all on the same box! Installation is very straight forward. Configuration is mostly straight forward. In the install guide there is a list of all of the things you'll need to know about your SQL Server config: things like the dbo password, database names, hostnames, ports, etc. Make sure that you have all of this information. It will make configuration much easier.

Things to be aware of:

1. SQL Server usernames, passwords, and objects are case sensitive, Oracle's are not by default. So if you have lower case or mixed case table names, for instance, in SQL Server you will end up having to double quote them when accessing them from Oracle SQL. For example, assume that you have a table called Foo on SQL Server and a database link for the gateway called sqlsrvr. You would need to issue the following from SQL*Plus to count all the rows:

select count(*) from "Foo"@sqlsrvr

Which leads to #2-

2.You will want to create a bunch of synonyms -- create public synonym foo for "Foo"@sqlsrvr -- for the objects on the SQL Server database. That way your Oracle scripts, programs, stored procedures, etc. don't need to worry about the case differences.

3. You can do joins between Oracle and SQL Server and can modify SQL Server data also. You can do distributed transactions that perform DML on both data sources and Oracle will perform a Two Phase Commit. Caveat - there can only be one foreign data source in a distributed transaction.

4. Your Oracle client does not connect directly through the gateway to SQL Server. It connects first to an Oracle database, which then connects to the gateway. This may seem trivial, but it confuses a lot of people. As part of the installation, you will add an entry for the gateway to your Oracle server's (not your client's) tnsnames file. Then you will create a database link for the gateway -- create public database link sqlsrvr using TG4SS. This example assumes that your TNS entry is called TG4SS. Now, your programs (SQL*Plus, Forms, etc) will not connect to the Oracle database FIRST. But, since you've created database links and public synonyms, the remote objects can be referenced by a simple name (which is good for your developers) and the database will make the connection to the gateway and over to SQL Server on your behalf.

5. The gateway uses an ODBC driver to connect to SQL Server. General knowledge of how ODBC works is helpful.

Hope this helps,

Tim Taylor
Senior Sales Consultant
Oracle Government
Bethesda, MD

vxf_at_akc.org wrote:

> Help!!
>
> Is anyone using this in production? My environment is Oracle 7.3.4.1 on HPUX
> 10.20 and SQL Server 6.5 on NT 4.0. I have been tasked with making these two
> databases talk to each other on-line.
>
> Anyone experienced with this, PLEASE SPEAK UP as to challenges and
> resolutions.
>
> vxf_at_akc.org
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
Received on Thu Jul 16 1998 - 15:37:40 CDT