CODEWITHSUNDEEP
sql-serverdatabaseoracle-databaseheterogeneous-services
ziggy
ziggy

Reputation: 15876

Connecting Oracle to SQL Server via database link

I am trying to connect from Oracle 10G(on UNIX) to a SQL Server database (On windows). I have looked at the manual and i will admit that i am finding the documentation quite difficult to follow. There seems to be several options to use but none of the documentation describe how each option works.

As an example, i have been given the following information on the database i need to connect to (i.e. the SQL Server database)

To connect the above i made the following changes

$ORACLE_HOME/hs/admin/inithsodbc.ora

  HS_FDS_CONNECT_INFO = data_extract
  HS_FDS_TRACE_LEVEL = 0

$ORACLE_HOME/network/admin/tnsnames.ora

  sqlserver.db =
          (DESCRIPTION =
                  (ADDRESS = (protocol=tcp)(host=10.10.10.10)(port=49400))
                          (connect_data = (sid=data_extract))
                          (hs=ok)
          )

$ORACLE_HOME/network/admin/listener.ora

  LISTENER =
    (DESCRIPTION_LIST =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = merlin)(PORT = 1525))
      )
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
    )

  SID_LIST_LISTENER =
    (SID_LIST =
      (SID_DESC =
        (SID_NAME = PLSExtProc)
        (ORACLE_HOME = /u/app/oracle/product/10.2.0/db)
        (PROGRAM = extproc)
      )
      (SID_DESC =
        (GLOBAL_DBNAME = db1.mydb.co.uk)
        (ORACLE_HOME = /u/app/oracle/product/10.2.0/db)
        (SID_NAME = billdb)
      )
      (SID_DESC =
          (SID_NAME = data_extract)
          (ORACLE_HOME = /u/app/oracle/product/10.2.0/db)
          (program = hsodbc)
      )
    )

Note: In listener.ora, i only added the last SID_DESC entry.

I then went on and created the database link as shown below

  create database link sqlservdb using 'sqlserver.db';

When i try to access a table i get the following error

  sqlplus> select * from TESTTABLE@sqlservdb;
  select * from TESTTABLE@sqlservdb
                             *
  ERROR at line 1:
  ORA-28545: error diagnosed by Net8 when connecting to an agent
  Unable to retrieve text of NETWORK/NCR message 65535
  ORA-02063: preceding 2 lines from ORASQLSERVER

A couple of things i am not sure of

Any help with the above will be appreciated. Thanks

Upvotes: 9

Views: 43312

Answers (2)

oscar van zelst
oscar van zelst

Reputation: 31

In order to create a database link to SQL Server, you have to enclose your connect name and password between double quotes, like below: 

create database link mysqlink 
connect to "myuser" identified by "mypassword" using 'servername';

Upvotes: 3

cmutt78
cmutt78

Reputation: 859

I used Data Direct drivers which had to be configured on the Oracle UNIX machine. You must create the ODBC connection in UNIX in order to be able to see SQL Server. (At least that has always been how I have done it)

Upvotes: 4

Related Questions