| error ORA-02019: while selecting table from oracle10g to SQL Server 2005 [message #455124] |
Mon, 10 May 2010 06:18  |
oradba123
Messages: 86
Registered: June 2009
Location: india
|
Member |
|
|
Hi all,
our oracle 10g database is on AIX 5.2 and i configures heterogeneous service and install oracle 10g cleint on windows 2003 server where SQL Server reside . i configured the following things but i got the error *ORA-02019*. please help me its very urgent. quick response will higly appreciated. thanks alot in advance.
1)create Sytem DSN (mysqlserver) by selectin SQL Native cleint
2)copy the existing entry and paste it in listener.ora file and edit it as below
LISTENERMYSQLSERVERDSN =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST= hostname of oracle database)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
SID_LIST_ =
(SID_LIST=
(SID_DESC=
(SID_NAME= mysqlserver)
(ORACLE_HOME = /u01/app/oracle/product/101)
(PROGRAM=hsodbc)
)
)
$lsnrctl status LISTENERMYSQLSERVERDSN
It is working fine
3) configure TNAMES.ora
mysqlserver =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = 100.100.50.5)
(PORT = 1521)
)
(CONNECT_DATA =
(SID = mysqlserver)
(HS = OK)
)
)
c:>tnsping mysqlserver
it is workin fine
4)copy inithsodbc.ora and make it initmysqlserver.ora
HS_FDS_CONNECT_INFO = mysqlserver
HS_FDS_TRACE_LEVEL = off
4)create the database link
sql>create database link mysqlserver
connect to "sa" identified by "nsp@123" using 'mysqlserver';
when i selecting SQL Server object from oracle i got below error
SQL> select * from NSP_products@mysqlserver;
select * from NSP_products@mysqlserver
*
ERROR at line 1:
ORA-02019: connection description for remote database not found
pLease help me its very urgent.
BEST REGARD
|
|
|
|
|
|
| Re: error ORA-02019: while selecting table from oracle10g to SQL Server 2005 [message #455235 is a reply to message #455165] |
Mon, 10 May 2010 13:24  |
oradba123
Messages: 86
Registered: June 2009
Location: india
|
Member |
|
|
Hi Blackswan,
thanks for your quick response.
yes we have two system one IBM AIX 5.2 and second one is windows 2003.
1)ON IBM AIX Machine Ip Address is100.100.50.5 ,ORACLE 10g DATABASE is there.
2)ON WINDOWS 2003 Server IP address is 172.16.11.18, Microsoft SQL Server 2005 installed it, ORACLE 10g CLEINT is installed on it
$lsnrctl status LISTENERMYSQLSERVERDSN
it is working fine
c:>tnsping mysqlserver
it is working fine
NSP_products is SQL server database table.
SQL> select * from [email]NSP_products@mysqlserver;
select * from [email]NSP_products@mysqlserver
*
ERROR at line 1:
ORA-02019: connection description for remote database not found
pLease help me its very urgent.
BEST REGARD
[Updated on: Mon, 10 May 2010 13:33] Report message to a moderator |
|
|
|
| Re: error ORA-02019: while selecting table from oracle10g to SQL Server 2005 [message #455291 is a reply to message #455165] |
Tue, 11 May 2010 00:07 |
oradba123
Messages: 86
Registered: June 2009
Location: india
|
Member |
|
|
Hi Blackswan,
thanks for your quick response.
yes we have two system one IBM AIX 5.2 and second one is windows 2003.
1)ON IBM AIX Machine Ip Address is100.100.50.5 ,ORACLE 10g DATABASE is there.
2)ON WINDOWS 2003 Server IP address is 172.16.11.18, Microsoft SQL Server 2005 installed it, ORACLE 10g CLEINT is installed on it
$ lsnrctl status LISTENERMYSQLSERVERDSN
LSNRCTL for IBM/AIX RISC System/6000: Version 10.1.0.2.0 - Production on 11-MAY-2010 07:48:10
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=100.100.50.5)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENERMYSQLSERVERDSN
Version TNSLSNR for IBM/AIX RISC System/6000: Version 10.1.0.2.0 - Production
Start Date 10-MAY-2010 13:12:29
Uptime 0 days 18 hr. 35 min. 41 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/101/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/101/network/log/listenermysqlserverdsn.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=100.100.50.5)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ysccapps.yamama.com)(PORT=8080))(Presentation=HTTP)(Session=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ysccapps.yamama.com)(PORT=2100))(Presentation=FTP)(Session=RAW))
Services Summary...
Service "mysqlserver" has 1 instance(s).
Instance "mysqlserver", status UNKNOWN, has 1 handler(s) for this service...
Service "yscc02.ysccapps.yamama.com" has 1 instance(s).
Instance "yscc02", status READY, has 1 handler(s) for this service...
Service "yscc02XDB.ysccapps.yamama.com" has 1 instance(s).
Instance "yscc02", status READY, has 1 handler(s) for this service...
The command completed successfully
c:>tnsping mysqlserver
TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 - Production on 11-MAY-
010 07:51:32
Copyright (c) 1997, 2003, Oracle. All rights reserved.
Used parameter files:
E:\Oracle\product\10.1.0\Client_2\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 100.10
.50.5) (PORT = 1521)) (CONNECT_DATA = (SID = mysqlserver) (HS = OK)))
OK (0 msec)
FROM Database10g side (IBM AIX side)
SQL> show user
USER is "NEW"
I created Database link
SQL> create database link mysqlserver
2 connect to "sa" identified by "nsp@123" using 'MYSQLSERVER';
Database link created
Whwn i fire a select statement SQL> select * from NSP_products@mysqlserver;
select * from NSP_products@mysqlserver
*
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified
PLease help me .thanks a lot for pervius reply
|
|
|
|
| Re: error ORA-02019: while selecting table from oracle10g to SQL Server 2005 [message #455356 is a reply to message #455165] |
Tue, 11 May 2010 05:56 |
oradba123
Messages: 86
Registered: June 2009
Location: india
|
Member |
|
|
Hi Blackswan,
thanks for your quick response.
yes we have two system one IBM AIX 5.2 and second one is windows 2003.
1)ON IBM AIX Machine Ip Address is100.100.50.5 ,ORACLE 10g DATABASE is there.
2)ON WINDOWS 2003 Server IP address is 172.16.11.18, Microsoft SQL Server 2005 installed it, ORACLE 10g CLEINT is installed on it
Please see my listener.ora file on AIX machine
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = yscc02)
(ORACLE_HOME = /u01/app/oracle/product/101)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ysccapps.yamama.com)(PORT = 1521))
)
)
)
LISTENERMYSQLSERVERDSN =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST= 100.100.50.5)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
SID_LIST_LISTENERMYSQLSERVERDSN =
(SID_LIST=
(SID_DESC=
(SID_NAME= mysqlserver)
(ORACLE_HOME = /u01/app/oracle/product/101)
(PROGRAM=hsodbc)
)
)
>$lsnrctl status LISTENERMYSQLSERVERDSN
LSNRCTL for IBM/AIX RISC System/6000: Version 10.1.0.2.0 - Production on 11-MAY-2010 13:46:46
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=100.100.50.5)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENERMYSQLSERVERDSN
Version TNSLSNR for IBM/AIX RISC System/6000: Version 10.1.0.2.0 - Production
Start Date 11-MAY-2010 13:05:07
Uptime 0 days 0 hr. 41 min. 39 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/101/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/101/network/log/listenermysqlserverdsn.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=100.100.50.5)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ysccapps.yamama.com)(PORT=8080))(Presentation=HTTP)(Session=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ysccapps.yamama.com)(PORT=2100))(Presentation=FTP)(Session=RAW))
Services Summary...
Service "mysqlserver" has 1 instance(s).
Instance "mysqlserver", status UNKNOWN, has 1 handler(s) for this service...
Service "yscc02.ysccapps.yamama.com" has 1 instance(s).
Instance "yscc02", status READY, has 1 handler(s) for this service...
Service "yscc02XDB.ysccapps.yamama.com" has 1 instance(s).
Instance "yscc02", status READY, has 1 handler(s) for this service...
The command completed successfully
C:\>tnsping mysqlserver
TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 - Production on 11-MAY-2
010 13:37:42
Copyright (c) 1997, 2003, Oracle. All rights reserved.
Used parameter files:
E:\Oracle\product\10.1.0\Client_2\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 100.100
.50.5) (PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME=mysqlserver) (HS = OK)))
OK (10 msec)
Stil aim getting the sam error.
SQL> select * from NSP_products@mysqlserver;
select * from NSP_products@mysqlserver
*
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified
any suggetion or help for. thanks in advance
BEST REGARDS
|
|
|
|
| Re: error ORA-02019: while selecting table from oracle10g to SQL Server 2005 [message #455425 is a reply to message #455356] |
Tue, 11 May 2010 09:05 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
>C:\>tnsping mysqlserver
>
>TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 - Production on 11-MAY-2
>010 13:37:42
>
>Copyright (c) 1997, 2003, Oracle. All rights reserved.
>
>Used parameter files:
>E:\Oracle\product\10.1.0\Client_2\network\admin\sqlnet.ora
>
>
>Used TNSNAMES adapter to resolve the alias
>Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 100.100
>.50.5) (PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME=mysqlserver) (HS = OK)))
>OK (10 msec)
>
I am not an expert on ODBC & last I actually used it was over a decade ago.
I would have expected the above to show HOST=172.16.11.18 since that is where SQL*Server resides.
So I am not surprised SERVICE_NAME=mysqlserver is not found on HOST = 100.100.50.5
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
