Home » RDBMS Server » Networking and Gateways » connectivity of sql server into oracle through heterogenius services (oracle 10g,windows xp sp2)
connectivity of sql server into oracle through heterogenius services [message #516194] Fri, 15 July 2011 07:55 Go to next message
lancer26
Messages: 50
Registered: May 2006
Location: Pakistan
Member

dear all,
i want to update table based on sql server view daily basis, for this, i did following steps
1. configure system dsn odbc named "ASMSQL"
2. create inithsodbc.ora file in hs/admin folder,
set parameter as

HS_FDS_CONNECT_INFO = ASMSQL
HS_FDS_TRACE_LEVEL = 0

complete file is attached.

3.edit TNSNAMES.ORA file in network/admin folder as following
hsodbc =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL=TCP)(HOST=localhost)(PORT=1521))
)
(CONNECT_DATA =(SID=hsodbc)
)
(HS=OK)
)
4. edit listener.ora file as following

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = hsodbc)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = hsodbc)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = developer-fpr.azgard9.com)(PORT = 1521))
)
)
5. finally create db link as following
CREATE PUBLIC DATABASE LINK "TESTSQL.REGRESS.RDBMS.DEV.US.ORACLE.COM"
CONNECT TO ORAFPR
IDENTIFIED BY <PWD>
USING 'hsodbc';
when i select data from that db link i got following error
*********************************************************************
Link : "TESTSQL.REGRESS.RDBMS.DEV.US.ORACLE.COM"
Error : ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'ORAFPR'. Reason: Not associated with a trusted SQL Server connection. (SQL State: 28000; SQL Code: 18452)
ORA-02063: preceding 2 lines from TESTSQL
*********************************************************************

through sql server client it's working fine but created error through oracle, kindly help.....
Re: connectivity of sql server into oracle through heterogenius services [message #516209 is a reply to message #516194] Fri, 15 July 2011 08:45 Go to previous messageGo to next message
BlackSwan
Messages: 22712
Registered: January 2009
Senior Member
problem seems to reside at SQL Server end & does not directly involve Oracle RDBMS.
Re: connectivity of sql server into oracle through heterogenius services [message #516293 is a reply to message #516209] Sat, 16 July 2011 04:07 Go to previous messageGo to next message
lancer26
Messages: 50
Registered: May 2006
Location: Pakistan
Member

well,i checked sql server login and password into sql server client.it is successfully login, but can't access into oracle.
also i am unable to connect through hsodbc service name into toad, it gives me error "ora-03135: connection lost contact".
tnsping result of hsodbc is ok..

please help
Re: connectivity of sql server into oracle through heterogenius services [message #516333 is a reply to message #516293] Sat, 16 July 2011 14:08 Go to previous messageGo to next message
Littlefoot
Messages: 19515
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Check this Maaher's message - perhaps it'll help (you may read the whole topic, though - it contains some useful links).
Re: connectivity of sql server into oracle through heterogenius services [message #516563 is a reply to message #516333] Tue, 19 July 2011 05:45 Go to previous messageGo to next message
lancer26
Messages: 50
Registered: May 2006
Location: Pakistan
Member

i did it as maaher did for ms access. i am successfully view ms access data, but when i connect and configure sql server, then i got same above error

*********************************************************************
Link : "TESTSQL.REGRESS.RDBMS.DEV.US.ORACLE.COM"
Error : ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'ORAFPR'. Reason: Not associated with a trusted SQL Server connection. (SQL State: 28000; SQL Code: 18452)
ORA-02063: preceding 2 lines from TESTSQL
*********************************************************************
please suggest
Re: connectivity of sql server into oracle through heterogenius services [message #519048 is a reply to message #516563] Tue, 09 August 2011 02:18 Go to previous message
lancer26
Messages: 50
Registered: May 2006
Location: Pakistan
Member

problem solved, problem at sql server end.thanx all specially black swan.
Previous Topic: alert log error Fatal NI connect error 12170
Next Topic: DB Link creation - No TNSNAMES.ora. Using LDAP
Goto Forum:
  


Current Time: Thu Aug 28 04:06:37 CDT 2014

Total time taken to generate the page: 0.10138 seconds