Oracle Database Discussions

Hi all,

I am trying to connect from Oracle 10G to a SQL Server database. 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

Edited by: ziggy on May 25, 2011 11:14 AM

Edited by: ziggy on May 25, 2011 11:18 AM

Edited by: ziggy on May 25, 2011 11:18 AM