Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, berend.

Asked: September 04, 2000 - 9:53 am UTC

Last updated: July 07, 2006 - 6:53 pm UTC

Version: 8.0.6.0.0

Viewed 1000+ times

You Asked

L.S.

How can I make a link to MS-SQLServer?
Or how can I link from MS-SQLServer to my Oracle database?

B.J. Otten

and Tom said...

From Oracle to SQLServer is done via a transparent gateway. It will allow for full read/write access to SQL Server with an automatic two phase commit (data integrity).

See
</code> http://www.oracle.com/gateways/ <code>
for details.

Rating

  (13 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, May 21, 2001 - 4:36 pm UTC


Generic Connectivity

sivababu, April 26, 2002 - 9:34 am UTC

Hello TOM,

Generic Connectivity is not working properly after configured.

    My Oracle 9i (personal edition ) IP  is 192.168.181.21 ( Windows 98 )
    Our Sqlserver 2000 Ip is  192.168.181.4  (windows 2000)
   
    
  
   1. The odbc is created in System dsn. it is called hsodbc
   2.  The values from C:\oracle\ora91\hs\admin\inithsodbc.ora
    HS_FDS_CONNECT_INFO = hsodbc
    HS_FDS_TRACE_LEVEL = 0
   3. The tnsnames.ora from  C:\oracle\ora91\network\ADMIN\tnsnames.ora

    hsodbc =
      (DESCRIPTION=
        (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.181.21)(PORT=1521))  --> i tried both IP.
        (CONNECT_DATA=(SID=hsodbc))
        (HS=OK)
      ) 


    siva =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.181.21)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = siva)
        )
      )
    4. The listener.ora from C:\oracle\ora91\network\ADMIN\listener.ora


     LISTENER =
      (ADDRESS_LIST=
           (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
           (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

    SID_LIST_LISTENER =
    (SID_LIST=
        (SID_DESC =
          (GLOBAL_DBNAME = siva)
          (ORACLE_HOME = c:\oracle\ora91)
          (SID_NAME = siva)
        )
        (SID_DESC=
              (SID_NAME=hsodbc)
              (ORACLE_HOME=c:\oracle\ora91)
              (PROGRAM=hsodbc)
            )
      )

    5. The sqlnet.ora has this line.
        NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

     6.  If i will do the following steps i"m getting error.


    SQL> connect scott/tiger@siva
    Connected.
    SQL> create database link hsodbc
      2   CONNECT TO sa IDENTIFIED BY sa
      3  using 'hsodbc';

    Database link created.

    SQL> select * from abc@hsodbc;
    select * from abc@hsodbc
                      *
    ERROR at line 1:
    ORA-28509: unable to establish a connection to non-Oracle system
    ORA-02063: preceding line from HSODBC


 I checked the Error message in the documentation. But i couldn't get where the error is occurred.

 Thanks in advance and awaiting for your reply.


siva



 

Tom Kyte
April 26, 2002 - 9:50 am UTC

does the sqlnet.ora have anything else in it.

have you used odbctest to see if the system dsn is functioning.


listener.ora

mohammad toaha, April 26, 2002 - 1:40 pm UTC

For Mr.Sivababu,

Could you please try with dnsname or IP_address of your host instead of using 'Localhost' as a word as below:
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))

Please,let us know your result.


Tom Kyte
April 26, 2002 - 8:27 pm UTC

Thats it! the localhost is 127.0.0.1 -- there isn't a listener to connect to on the ip addresses they are trying! good eye.

SQL 2000?

Jer Smith, April 26, 2002 - 3:44 pm UTC

Two questions on these products:

This web page:
</code> http://www.oracle.com/gateways/gateway_bundles/gateways_datasheet.html <code>
implies that it only works with SQL Server 7? Will 2000 work?

Also, is there any Third Party or other product that will allow a link in the opposite direction? For many environments, that's the more common scenario for two databases (SQL Server on a front-end installation, and Oracle as Enterprise-level database)?

Tom Kyte
April 26, 2002 - 8:34 pm UTC

isn't sqlserver 2k version 7? lost track along time ago.

For the "opposite direction" -- you goto microsoft, yes, they have the gateway to us.

Other Third Party

A reader, April 26, 2002 - 8:21 pm UTC

To Jer Smith,

I believe there is a gateway from Information Builders (IBI) , </code> http://www.ibi.com, <code>it is called the EDA Gateway server. I belive it is the same thing as Oracle Transport Gateway.

I am not sure if Data Junction will do this. They are normally a one way system but they were attempting to enter the space.

Good Luck.

Tom, keep up the great work! Oracle is the best in our space.

Still not Connecting to Sqlserver 2000 ( version 8 )

sivababu, April 27, 2002 - 2:29 am UTC

Hello TOM,
Thanks for your immediate reply.
Regaring your First reply,
1. The hsodbc system dsn is successfully connecting.
2. The Sqlnet.ora has only one line What i mentioned.
Regarding your second reply,
1. I tried local host ip 127.0.0.1 and my network ip 192.168.181.21 (ie my machine with personal oracle 9i). But it is giving the same error.

For Listener log :


TNSLSNR for 32-bit Windows: Version 9.0.1.1.1 - Production on 27-APR-2002 11:46:06

Copyright (c) 1991, 2001, Oracle Corporation. All rights reserved.

System parameter file is c:\oracle\ora91\network\admin\listener.ora
Log messages written to c:\oracle\ora91\network\log\listener.log
Trace information written to c:\oracle\ora91\network\trace\listener.trc
Trace level is currently 0

Started with pid=4294389477
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sivababu)(PORT=1521)))

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
27-APR-2002 11:46:06 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=sivababu))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=150999297)) * status * 0

My computer name is sivababu and network username also sivababu.



I couldn't get from where this problem is coming. I'm working around this problem for the past few days.

Thanks and awaiting for your reply.

regards,
sivababu

Tom Kyte
April 27, 2002 - 8:32 am UTC

We need to start over and not here. Send me via email your:

tnsnames.ora
listener.ora
sqlnet.ora
inithsodbc.ora

from the SERVER this database link is created on and I'll take a look see.

can you tnsping this entry as well?

jortiz, May 19, 2004 - 11:57 am UTC

Hello there !!!

I Did what sivababu said at the begining and It worked ok immediatly !!!

I dind't have to do anything else !!


Reviewer:  sivababu  from INDIA 

Hello TOM,

Generic Connectivity is not working properly after configured.

    My Oracle 9i (personal edition ) IP  is 192.168.181.21 ( Windows 98 )
    Our Sqlserver 2000 Ip is  192.168.181.4  (windows 2000)
   
    
  
   1. The odbc is created in System dsn. it is called hsodbc
   2.  The values from C:\oracle\ora91\hs\admin\inithsodbc.ora
    HS_FDS_CONNECT_INFO = hsodbc
    HS_FDS_TRACE_LEVEL = 0
   3. The tnsnames.ora from  C:\oracle\ora91\network\ADMIN\tnsnames.ora

    hsodbc =
      (DESCRIPTION=
        (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.181.21)(PORT=1521))  --> i tried 
both IP.
        (CONNECT_DATA=(SID=hsodbc))
        (HS=OK)
      ) 


    siva =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.181.21)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = siva)
        )
      )
    4. The listener.ora from C:\oracle\ora91\network\ADMIN\listener.ora


     LISTENER =
      (ADDRESS_LIST=
           (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
           (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

    SID_LIST_LISTENER =
    (SID_LIST=
        (SID_DESC =
          (GLOBAL_DBNAME = siva)
          (ORACLE_HOME = c:\oracle\ora91)
          (SID_NAME = siva)
        )
        (SID_DESC=
              (SID_NAME=hsodbc)
              (ORACLE_HOME=c:\oracle\ora91)
              (PROGRAM=hsodbc)
            )
      )

    5. The sqlnet.ora has this line.
        NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

     6.  If i will do the following steps i"m getting error.


    SQL> connect scott/tiger@siva
    Connected.
    SQL> create database link hsodbc
      2   CONNECT TO sa IDENTIFIED BY sa
      3  using 'hsodbc';

    Database link created.

    SQL> select * from abc@hsodbc; --Here I got the answer from SQL Server

It was so good !
 

GET A RIGHT CONNECTION !

jortiz, May 19, 2004 - 11:57 am UTC

Hello there !!!

I Did what sivababu said at the begining and It worked ok immediatly !!!

I dind't have to do anything else !!


Reviewer:  sivababu  from INDIA 

Hello TOM,

Generic Connectivity is not working properly after configured.

    My Oracle 9i (personal edition ) IP  is 192.168.181.21 ( Windows 98 )
    Our Sqlserver 2000 Ip is  192.168.181.4  (windows 2000)
   
    
  
   1. The odbc is created in System dsn. it is called hsodbc
   2.  The values from C:\oracle\ora91\hs\admin\inithsodbc.ora
    HS_FDS_CONNECT_INFO = hsodbc
    HS_FDS_TRACE_LEVEL = 0
   3. The tnsnames.ora from  C:\oracle\ora91\network\ADMIN\tnsnames.ora

    hsodbc =
      (DESCRIPTION=
        (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.181.21)(PORT=1521))  --> i tried 
both IP.
        (CONNECT_DATA=(SID=hsodbc))
        (HS=OK)
      ) 


    siva =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.181.21)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = siva)
        )
      )
    4. The listener.ora from C:\oracle\ora91\network\ADMIN\listener.ora


     LISTENER =
      (ADDRESS_LIST=
           (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
           (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

    SID_LIST_LISTENER =
    (SID_LIST=
        (SID_DESC =
          (GLOBAL_DBNAME = siva)
          (ORACLE_HOME = c:\oracle\ora91)
          (SID_NAME = siva)
        )
        (SID_DESC=
              (SID_NAME=hsodbc)
              (ORACLE_HOME=c:\oracle\ora91)
              (PROGRAM=hsodbc)
            )
      )

    5. The sqlnet.ora has this line.
        NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

     6.  If i will do the following steps i"m getting error.


    SQL> connect scott/tiger@siva
    Connected.
    SQL> create database link hsodbc
      2   CONNECT TO sa IDENTIFIED BY sa
      3  using 'hsodbc';

    Database link created.

    SQL> select * from abc@hsodbc; --Here I got the answer from SQL Server

It was so good !
 

OK

A reader, May 19, 2004 - 12:10 pm UTC

I can send you my scripts and what I did step by step to help you with this !!


write to jortiz@avansoft.com only if it's neccesary !


generic connectivity vs oracle gateways

amit, June 29, 2006 - 11:09 am UTC

your (not ur) help always appreciated.

Now, We need to transfer data from oracle to sql server on daily basis(one table).

q1) I know we have two methods of doing that
1) generic connectivity
2) oracle gateways

Now my query is when to use which method ? Are there any pros and cons of using one over other ?


q2) after using above method can I do it from inside oracle database or I have to go sql server to get data ?

sql-oracle > insert into table_of_sqlserver@db_link select * from oracle_table;


q3) is Replication possible here !

Thanks always
Amit






Tom Kyte
June 29, 2006 - 11:11 am UTC

q1) if generic connectivity meets your needs - it would be by far the easiest approach.


q2) you would be able to query sqlserver from oracle, our gateways/generic connectivity stuff reach out FROM Oracle to Other database.

q3) but not practical, you could technically create a full refresh materialized view - but not changes only refreshes.

Generic Connectivity or Gateway?

Jon, June 29, 2006 - 12:35 pm UTC

Generic Connectivity is free with the database and it uses an "ODBC or OLEDB driver to access any ODBC or OLEDB compliant non-Oracle system".
Reference: </code> http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14220/integrat.htm#sthref3361

The SQL Server Gateway comes at an additional cost and it "uses MS-ODBC driver for SQL server that comes with all windows installations".
Reference: 
http://download-east.oracle.com/docs/cd/B19306_01/gateways.102/b14270/ch1.htm#sthref15

So both use the same OLEDB or ODBC driver.

I've read that there are performance differences between the two.  The biggest performance difference I can see is the ability to write pass through SQL with a Gateway.  

http://download-east.oracle.com/docs/cd/B19306_01/gateways.102/b14270/ch3.htm#i1005577 <code>

If you are using a tool like Discoverer where you can't execute a stored procedure and rely on using database links to connect to SQL Server, is there any performance benefit of a Gateway to SQL Server versus Generic Connectivity since they are both using the same OLEDB or ODBC drivers?


equation

amit, July 03, 2006 - 9:37 am UTC

<quote>
if generic connectivity meets your needs - it would be by far the easiest approach.
</quote>

SO, if the following equation is right

gateways = generic connectivity + few more things.

what are these few more things ? (except cost )


Tom Kyte
July 07, 2006 - 6:53 pm UTC

native drivers in some cases... providing more functionality.

functionality

amit, July 11, 2006 - 9:36 am UTC

<quote>
native drivers in some cases... providing more functionality.
</quote>


what extra functionality ?

Thanks