9

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

ziggy
  • 15,677
  • 67
  • 194
  • 287
  • what platform is your oracle database running on? – cmutt78 May 25 '11 at 01:37
  • Oracle 10G is running on Unix and the SQL Server database is running on Windows. Thanks – ziggy May 25 '11 at 09:34
  • Did you try to provide user/password? `CREATE DATABASE LINK sqlservdb CONNECT TO myuser IDENTIFIED BY mypass USING 'sqlserver.db';` Did you try a connect name without dot (e.g. `sqlserver`)? – arnep May 25 '11 at 11:44
  • Could someone please confirm if i need to install a 3rd party driver for this to work. i am seeing conflicting information on several articles. – ziggy May 25 '11 at 13:16

2 Answers2

4

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)

cmutt78
  • 861
  • 1
  • 9
  • 18
3

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';
Viccari
  • 9,029
  • 4
  • 43
  • 77
oscar van zelst
  • 31
  • 1