2

apologies if this question has already been asked, i just couldn't find the answer to my case. I'm trying to make a connection (link) between Oracle 11g MS SQL database which are on 2 different servers. I've followed the instruction on this link

http://www.dba-oracle.com/t_heterogeneous_database_connections_sql_server.htm

Only that in my listener on SQL server, I'm using DG4ODBC rather than hsodbc

i've listed the steps below, but i can't figure out how to resolve it.

1) installed oracle client on my SQL server. 2) created an ODBC (64 bit) connection in the SQL server to point to my SQL target database called dg4odbc 3) Created file called initdg4odbc.ora in

D:\app\user\product\11.2.0\client_2\hs\admin with the below content:

# HS init parameters
#
HS_FDS_CONNECT_INFO = dg4odbc
HS_FDS_TRACE_LEVEL = on

4) Updated my listener to be as below:

LISTENER =
(ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=sqlserver)(PORT=1521))
)
SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=dg4odbc)
         (ORACLE_HOME=D:\app\user\product\11.2.0\client_2)
         (PROGRAM=dg4odbc)
      )
  )

#CONNECT_TIMEOUT_LISTENER = 0

5) when I stop and start the listener, I get the below message:

Instance "dg4odbc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

6) on my Oracle database server, updated the tnsnames.ora file to include:

dg4odbc.world = (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)
               (HOST = sqlserver)
               (PORT = 1521)
          )
          (CONNECT_DATA = (SID=dg4odbc)
          )
          (HS=OK)
     )

7) when I try to ping the tns using : tnsping dg4odbc I get the following error:

Used parameter files:
E:\oracle\product\11.2.0.4\dbhome_1\network\admin\sqlnet.ora

TNS-03505: Failed to resolve name

Could you please tell me where I am going wrong? Im getting a bit desperate to get this connection to work.

really appreciate your help on this.

Thanks

Mojatba
  • 39
  • 1
  • 5
  • I'm not sure that I'm following what you have installed on which server. Let O be the Oracle server and S be the SQL Server server. The simplest possible configuration would be to create an ODBC data source on O that connects to S and to configure the listener.ora, tnsnames.ora, and init<>.ora on O. It sounds like you may have installed a number of things on S that wouldn't generally make sense – Justin Cave Jul 06 '15 at 03:01
  • thanks for your reply, only in Oracle server. 1) Created an ODBC connection to SQL server called msqlserver. 2) added an entry to the Listener again in Oracle as below: SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=dg4odbc) (ORACLE_HOME=E:\Oracle\product\11.2.0.4\dbhome_1) (PROGRAM=dg4odbc) ) ) – Mojatba Jul 06 '15 at 20:12
  • and then added a new entry in tnsnames again on Oracle dg4odbc.world = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = sqlserver) (PORT = 1521) ) (CONNECT_DATA = (SID=dg4odbc) ) (HS=OK) ) – Mojatba Jul 06 '15 at 20:16
  • so nothing to do with SQL server. I still get the same error as TNS-03505: Failed to resolve name – Mojatba Jul 06 '15 at 20:17
  • It's old, but I'll reply nevertheless. You have a local error, it means that what you are trying to tnsping does not exist (the name resolution message might be a little bit misleading because tnsname is not the only methodology, but more likely you are using tnsnames). Either your tnsnames.ora entry is wrongly formed, you modified the wrong tnsnames.ora or specified the wrong string on the tnsping. Try another entry you have on the same file to make sure. – Víctor Apr 09 '18 at 20:30

0 Answers0