Sql-server – Linking Oracle 10g XE with SQL server using ODBC

listenerodbcoraclesql server

I am connecting Oracle 10 XE with an SQL server 2008 using ODBC in windows 7.

I have successfully done following steps:

  1. Configured ODBC and test it, runs ok!
  2. Configured listener in tnsnames.ora and listener.ora
  3. Started listener from command prompt
  4. Created database link from interface SQL command line

After creating database link, when i issue a query to SQL server an error is returned: ORA-12154: TNS:could not resolve the connect identifier specified.

Below are the contents of *.ora files:

***************************** listener.ora *****************

LISTENERSQLSERVERDSN =
 (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

SID_LIST_LISTENERSQLSERVERDSN=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=SQLSERVERDSN)
         (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
         (PROGRAM=hsodbc)
       )
      )

************************tnsnames.ora************

SQLSERVERDSN  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))
    (CONNECT_DATA=
    (SERVER=DEDICATED)
    (SID_NAME= SQLSERVERDSN))
    (HS=OK)
  )

*********************sqlnet.ora***********

SQLNET.AUTHENTICATION_SERVICES = (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Please help since i have tried almost every solution found.

thanks,

Best Answer

Use the parameter NAMES.DEFAULT_DOMAIN to set the domain from which the client most often looks up names resolution requests. When this parameter is set, the default domain name is automatically appended to any unqualified net service name or service name.

For example, if the default domain is set to us.acme.com, the connect string CONNECT scott/tiger@sales gets searched as sales.us.acme.com. If the connect string includes the domain extension, such as CONNECT scott/tiger@sales.acme.com, the domain is not appended.

Related Question