Connecting Oracle to SQL Server via database link

Question

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)

• Username
• Password
• Database Name [lets assume the database name is data_extract]

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

• Where do i specify the username and password to access the sql server from the oracle db to the sql server db
• Having read around on the internet, i can see some people refering to a DSN datasource. I am told that the information that i have is all i need. Is this correct or do i need something else?
• The sid i specified in inithsodbc.ora and tnsnames.ora is actually the sql server database. Is this correct?

Any help with the above will be appreciated. Thanks

Answer 1

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)