Please help.. I need to connect to a SQL Server database (B) from an Oracle database (A) which are both on Windows server machines. Here is what I did.
System DS created on (A):- Name: ISECWM Driver: ODBC Driver 11 for SQL Server Test Results:
Microsoft ODBC Driver for SQL Server Version 12.00.5543
Running connectivity tests...
Attempting connection Connection established Verifying option settings Disconnecting from server
TESTS COMPLETED SUCCESSFULLY!
$ORACLE_HOME/hs/admin/initISECWM.ora file contains
HS_FDS_CONNECT_INFO = ISECWM HS_FDS_TRACE_LEVEL = OFF
$ORACLE_HOME/network/admin/listener.ora updated:-
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME=
ISECWM) (ORACLE_HOME=c:\oracle\11g) (PROGRAM=dg4odbc) ) )
$ORACLE_HOME/network/admin/tnsnames.ora updated:-
WORLDMAP.DBL = (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=
SERVER A HOSTNAME)(PORT=1521)) (CONNECT_DATA=(SID=ISECWM)) (HS=OK) )
Database link created:-
CREATE PUBLIC DATABASE LINK TSTDBLINK CONNECT TO "sql server username" IDENTIFIED BY "sql server password" USING 'WORLDMAP.DBL';
Tested the database link with:-
select * from dual@TSTDBLINK; --gives the error
Error:-
ORA-28513: internal error in heterogeneous remote agent ORA-02063: preceding line from TSTDBLINK 28513. 00000 - "internal error in heterogeneous remote agent" *Cause: An internal error has occurred in the Oracle remote agent supporting a heterogeneous database link. *Action: Make a note of how the error was produced and contact the customer support representative of the agent vendor. Error at Line: 7 Column: 20
Ah, finally Oracle helped me with the issue.
It seems I had 2 homes, I put the initISECWM.ora in the wrong Oracle home.
Just copied and pasted it in the other home/hs/admin/ and it worked like a wonder! Voila!