All databases share a common set of normalized SQL, which, in theory, allows them to interoperate directly using database links.
However, reality is not so simple, as those who've tried to connect DB2 with SQLServer might have realized.

Luckily, with Oracle, there are at least two ways to achieve direct SQL*NET connectivity to foreign databases: Oracle Heterogeneous Services ODBC (HSODBC) and Oracle Transparent Gateways.

Here, we will achieve a simple database link between an Oracle database on a UNIX server and an SQLServer database residing on a Windows Server 2003 machine through the simplest of the two methods: Oracle Heterogeneous services. Bluntly, it consists in installing an Oracle pseudo-listener on the target non-Oracle database server.

As Microsoft doesn't provide any sort of UNIX client for SQL Server, all this interoperability is achieved thanks to work done by Oracle coders. Kudos to them, and  the opposite to the other guys.
 
X = Windows Server 2003 with SQLServer 2005 + Oracle 8iR3 with Oracle HS
Y = Solaris 8 server with Oracle 8iR3 + Heterogeneous Services installed.
 
 
On X:

Step 0) On X: Install Oracle Server 8iR3 software or later with Heterogenous Database connectivity (Check that ODBC DRIVERS have really been installed). I won't detail the installation of Oracle on Windows here.
 
Step 1) On X: Configure DSN:
Go to: Settings -> Control Panel. Double-click on ODBC icon.
Then click on the System DSN tab and Add button. Add SQL Server, as local server. Name it, for example, MSQL (we will be using "MSQL" in our example configuration files from now on).
Test it. The default database is the on we're targetting.
 
Step 2) On X :  Copy the file inithsodbc.ora into initMSQL.ora in the  $ORACLE_HOME\hs\admin directory (If you'd named the DSN "ZOZO" in the previous step, you would have named the file initZOZO.ora, of course).
 
Step 3) On X: Modify  the initMSQL.ora file in the following manner (HS_FDS_CONNECT_INFO must have the same name as the DSN):

###########
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = MSQL
HS_FDS_TRACE_LEVEL = NO
###########
 
Step 4) On X:  Modify the listener.ora file in $ORACLE_HOME\network\admin directory in the following manner (you're modifying the  SID_LIST_LISTENER paragraph. SID_NAME must be the same as the DSN) :
 
###########
SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=MSQL)
         (ORACLE_HOME = c:\Orant)
         (PROGRAM=hsodbc)
       )
      )
 ###########

Another solution would be to add an altogether new listener, that you've called MSQL, like this (here, we've set it to listen on port 1522):

###########
MSQL =
 (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
      (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
 
SID_LIST_MSQL=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=MSQL)
         (ORACLE_HOME = c:\Orant)
         (PROGRAM=hsodbc)
       )
      )
###########

In that case, you would have to start this listener specifically by issuing a lsnrctl start MSQL
 
Step 5) On X: restart the listener by issuing a  lsnrctl reload.
 
 
On Y:
 
Step 1) On Y: set GLOBAL_NAMES=FALSE  in your database's init.ora  file or in the initialization parameters.
 
Step 2) On Y: Add an entry pointing at server X listener by adding the following lines to the tnsnames.ora file:
 
###########
testMSQL  =
  (DESCRIPTION=
    (ADDRESS=
        (PROTOCOL=tcp)
        (HOST=SERVER_X_IP_ADDRESS)
        (PORT=SERVER_X_LISTENER_PORT)
     )
     (CONNECT_DATA=
        (SID=MSQL)
     )
     (HS=OK)
  )
###########
 
Step 3) On Y: Test connectivity by issuing a tnsping testMSQL
 
Step 4) On Y: Create the database link between your Oracle database and testMSQL by issuing the following SQL command:
SQL> create public database link testingMSQL connect to USER identified by PASSWORD using 'testMSQL';
 
Step 5) On Y: Do some selects on Server X's tables:
SQL> select * from TABLE_NAME@testingMSQL;

You're done!

Beware that you're restricted to normalized SQL. between the two databases. Old-timers will find themselves back in Oracle 6 days: You won't be able to use INSERT SELECT statements or other Oracle enhancements, but will have to go through a cursor, etc... However, you will be able to issue simple SELECT, INSERT and UPDATE commands. Which is what you wanted in the first place.

Happy computing.

Drop me a comment if this post has been useful to you, or if you see any reason for add-on or modification.

Nixman