| Read Data from MS Sql Server - Oracle Client [message #613876] |
Tue, 13 May 2014 05:45  |
 |
bijuandenb
Messages: 3
Registered: January 2012
|
Junior Member |
|
|
I wanted to access to the data from tables of MS Sql Server residing on a Server, using an Oracle Client 11g through SQL Plus.
Had setup ODBC from Oracle Database Server, TNSPing was working ok.
But from my PC, through SQL, it gives below error on an sql statement as well as TNSPING.
TNSPING EMPWR
TNS-03505: Failed to resolve name
SQLPLUS>SELECT COUNT (*) FROM DBO_LEAVETRANSACTIONS@EMWR;
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from EMPWR
Can someone pls explain to me the steps which I have followed are correct or not?
And what could be the reason for the above errors.
What is the best way to read data from SQL Server, through an Oracle Client PC, in a Client Server Environment.
Would appreciate some quick favorable replies, since I am struck with the matter for last few days.
Thanks
Biju
********************
My Environment is:
Oracle 11g database 64 bit on Windows 2008 Server IP:10.10.0.46 FULL COMPUTER NAME: Fjtco-ho-svr-05.fjtco-ho.fjtco.com
MS Sql Server on Windows 2003 Server IP: 10.10.0.42 FULL COMPUTER NAME: Fjtco-ho-svr-02.fjtco-ho.fjtco.com
Client: Windows 7 Prof 64 bit, Oracle Client 11g
Done the following steps:
1.
Have set up the ODBC System DSN in Or DB Server-Empwr. Tested the Connection and Ok.
2.
InitEmpwr.ora file created in the Or Db Server as follows.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO=Empwr
HS_FDS_TRACE_LEVEL=0
3.
Listener.ora of Or Db modified as follows.
# listener.ora Network Configuration File: D:\app\administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=Empwr)
(ORACLE_HOME=D:\app\administrator\product\11.2.0\dbhome_1)
(PROGRAM=hsodbc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = Fjtco-ho-svr-05.fjtco-ho.fjtco.com)(PORT = 1521))
)
)
ADR_BASE_LISTENER = D:\app\administrator
4.
Tnsnames.ora in OrDb Server is modified as below.
# tnsnames.ora Network Configuration File: D:\app\administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Fjtco-ho-svr-05.fjtco-ho.fjtco.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
Empwr =
(DESCRIPTION=
(ADDRESS = (PROTOCOL = TCP)(HOST = Fjtco-ho-svr-05.fjtco-ho.fjtco.com)(PORT=1521))
(CONNECT_DATA = (SID=Empwr))
(HS=OK)
)
5.
Created a Database Link in the Or DbServer as
CREATE PUBLIC DATABASE LINK connect to "sa" identified by "rational" using 'EMPWR';
6.
Lisetner Service restarted, did TNSPING from the Or Server, returned Ok.
7.
In my PC, added the EMPWR, in the tnsnames.ora as:
# tnsnames.ora Network Configuration File: D:\app\administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
NEW.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.0.46)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ORCL)
)
)
EMPWR =
(DESCRIPTION=
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.0.46)(PORT=1521))
(CONNECT_DATA = (SID=EMPWR))
(HS=OK)
)
8.
Ran TNSPING from my PC, gave error as:
TNS-03505: Failed to resolve name
Through SQL Plus in my PC, tried to run an SQL Statmt. but gave the error.
SQLPLUS>SELECT COUNT (*) FROM DBO_LEAVETRANSACTIONS@EMWR;
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from EMPWR
No ODBC set or listener file in my PC.
I can TNSPING from my PC to the Oracle Db ORCL successfully and run any sql on that.
***************
Anyone pls help. Thanks in advance..
|
|
|
|
| Re: Read Data from MS Sql Server - Oracle Client [message #613886 is a reply to message #613876] |
Tue, 13 May 2014 06:55  |
|
bijuandenb
Messages: 3
Registered: January 2012
|
Junior Member |
|
|
Dear all, I had misplaced the tnsnames.ora file in a different folder, than the required Oracle Client folder
Now it is placed correctly, so now TNSPING EMPWR is pinging OK
The second part, I run an SQL command in SQLPlus, in my client PC, but still it gives the following Error:
SQLPLUS>SELECT COUNT (*) FROM DBO_LEAVETRANSACTIONS@EMPWR;
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from EMPWR
Can someone pls look and help me urgently.
Thanks.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
