Home » RDBMS Server » Networking and Gateways » Read Data from MS Sql Server - Oracle Client (Oracle 11g)
Read Data from MS Sql Server - Oracle Client [message #613876] Tue, 13 May 2014 05:45 Go to next message
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 Go to previous message
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.
Previous Topic: how to connect oracle forms with ms sql server 2008
Next Topic: database connectivity over WAN
Goto Forum:
  


Current Time: Sat Sep 20 15:16:14 CDT 2014

Total time taken to generate the page: 0.04108 seconds