Home » RDBMS Server » Networking and Gateways » UNABLE TO TEST DBLINK BETWEEN ORACLE XE and MSSQL (ORACLE 11g XE windows 2008)
UNABLE TO TEST DBLINK BETWEEN ORACLE XE and MSSQL [message #591616] Tue, 30 July 2013 04:22 Go to next message
sakamboh
Messages: 24
Registered: March 2013
Location: oman
Junior Member
Hello All,

can anyone please guide me the below issue, i have created a dblink between oracle and mssql and tried testing the link its giving me below error. Can anyone please help me out with this.

SQL Error: ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified {IM002}
ORA-02063: preceding 2 lines from MSSQL
28500. 00000 -  "connection from ORACLE to a non-Oracle system returned this message:"
*Cause:    The cause is explained in the forwarded message.
*Action:   See the non-Oracle system's documentation of the forwarded
           message.


I have checked the dsn data source i have created, i am able to test the DSN successfully.
Re: UNABLE TO TEST DBLINK BETWEEN ORACLE XE and MSSQL [message #591619 is a reply to message #591616] Tue, 30 July 2013 04:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But the db link is unable to access it.
Post your db link definition and all relevant information about the configuration (dsn configuration, sqlnet.oa...).

Regards
Michel
Re: UNABLE TO TEST DBLINK BETWEEN ORACLE XE and MSSQL [message #591626 is a reply to message #591619] Tue, 30 July 2013 04:49 Go to previous message
sakamboh
Messages: 24
Registered: March 2013
Location: oman
Junior Member
Please find below the required details and advise resolution.

Listner.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = MSSQL)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = dg4odbc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)




TNSNAMES.ORA

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

ORACLR_CONNECTION_DATA = 
  (DESCRIPTION = 
    (ADDRESS_LIST = 
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) 
    ) 
    (CONNECT_DATA = 
      (SID = CLRExtProc) 
      (PRESENTATION = RO) 
    ) 
  ) 
MSSQL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT=1521))
    )
    (CONNECT_DATA =
      (SID = MSSQL)
    )
  (HS = OK)
)


SQLNET.ORA

# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NTS)


initMSSQL.ora

# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = MSSQL
HS_FDS_TRACE_LEVEL = off


#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>




ODBC Data sourcerce Name in ORACLE Server is:  MSSQL



DBLINK

CREATE public DATABASE LINK MSSQL CONNECT TO mssql_user IDENTIFIED BY mssql_pwd USING 'MSSQL';

Previous Topic: ora-12505 error with sql developer
Next Topic: Error while creating listener using netca
Goto Forum:
  


Current Time: Thu Mar 28 15:32:02 CDT 2024