A reader, May 21, 2001 - 4:36 pm UTC
Generic Connectivity
sivababu, April 26, 2002 - 9:34 am UTC
Hello TOM,
Generic Connectivity is not working properly after configured.
My Oracle 9i (personal edition ) IP is 192.168.181.21 ( Windows 98 )
Our Sqlserver 2000 Ip is 192.168.181.4 (windows 2000)
1. The odbc is created in System dsn. it is called hsodbc
2. The values from C:\oracle\ora91\hs\admin\inithsodbc.ora
HS_FDS_CONNECT_INFO = hsodbc
HS_FDS_TRACE_LEVEL = 0
3. The tnsnames.ora from C:\oracle\ora91\network\ADMIN\tnsnames.ora
hsodbc =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.181.21)(PORT=1521)) --> i tried both IP.
(CONNECT_DATA=(SID=hsodbc))
(HS=OK)
)
siva =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.181.21)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = siva)
)
)
4. The listener.ora from C:\oracle\ora91\network\ADMIN\listener.ora
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
SID_LIST_LISTENER =
(SID_LIST=
(SID_DESC =
(GLOBAL_DBNAME = siva)
(ORACLE_HOME = c:\oracle\ora91)
(SID_NAME = siva)
)
(SID_DESC=
(SID_NAME=hsodbc)
(ORACLE_HOME=c:\oracle\ora91)
(PROGRAM=hsodbc)
)
)
5. The sqlnet.ora has this line.
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
6. If i will do the following steps i"m getting error.
SQL> connect scott/tiger@siva
Connected.
SQL> create database link hsodbc
2 CONNECT TO sa IDENTIFIED BY sa
3 using 'hsodbc';
Database link created.
SQL> select * from abc@hsodbc;
select * from abc@hsodbc
*
ERROR at line 1:
ORA-28509: unable to establish a connection to non-Oracle system
ORA-02063: preceding line from HSODBC
I checked the Error message in the documentation. But i couldn't get where the error is occurred.
Thanks in advance and awaiting for your reply.
siva
April 26, 2002 - 9:50 am UTC
does the sqlnet.ora have anything else in it.
have you used odbctest to see if the system dsn is functioning.
listener.ora
mohammad toaha, April 26, 2002 - 1:40 pm UTC
For Mr.Sivababu,
Could you please try with dnsname or IP_address of your host instead of using 'Localhost' as a word as below:
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
Please,let us know your result.
April 26, 2002 - 8:27 pm UTC
Thats it! the localhost is 127.0.0.1 -- there isn't a listener to connect to on the ip addresses they are trying! good eye.
SQL 2000?
Jer Smith, April 26, 2002 - 3:44 pm UTC
Two questions on these products:
This web page:
</code>
http://www.oracle.com/gateways/gateway_bundles/gateways_datasheet.html <code>
implies that it only works with SQL Server 7? Will 2000 work?
Also, is there any Third Party or other product that will allow a link in the opposite direction? For many environments, that's the more common scenario for two databases (SQL Server on a front-end installation, and Oracle as Enterprise-level database)?
April 26, 2002 - 8:34 pm UTC
isn't sqlserver 2k version 7? lost track along time ago.
For the "opposite direction" -- you goto microsoft, yes, they have the gateway to us.
Other Third Party
A reader, April 26, 2002 - 8:21 pm UTC
To Jer Smith,
I believe there is a gateway from Information Builders (IBI) , </code>
http://www.ibi.com, <code>it is called the EDA Gateway server. I belive it is the same thing as Oracle Transport Gateway.
I am not sure if Data Junction will do this. They are normally a one way system but they were attempting to enter the space.
Good Luck.
Tom, keep up the great work! Oracle is the best in our space.
Still not Connecting to Sqlserver 2000 ( version 8 )
sivababu, April 27, 2002 - 2:29 am UTC
Hello TOM,
Thanks for your immediate reply.
Regaring your First reply,
1. The hsodbc system dsn is successfully connecting.
2. The Sqlnet.ora has only one line What i mentioned.
Regarding your second reply,
1. I tried local host ip 127.0.0.1 and my network ip 192.168.181.21 (ie my machine with personal oracle 9i). But it is giving the same error.
For Listener log :
TNSLSNR for 32-bit Windows: Version 9.0.1.1.1 - Production on 27-APR-2002 11:46:06
Copyright (c) 1991, 2001, Oracle Corporation. All rights reserved.
System parameter file is c:\oracle\ora91\network\admin\listener.ora
Log messages written to c:\oracle\ora91\network\log\listener.log
Trace information written to c:\oracle\ora91\network\trace\listener.trc
Trace level is currently 0
Started with pid=4294389477
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sivababu)(PORT=1521)))
TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
27-APR-2002 11:46:06 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=sivababu))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=150999297)) * status * 0
My computer name is sivababu and network username also sivababu.
I couldn't get from where this problem is coming. I'm working around this problem for the past few days.
Thanks and awaiting for your reply.
regards,
sivababu
April 27, 2002 - 8:32 am UTC
We need to start over and not here. Send me via email your:
tnsnames.ora
listener.ora
sqlnet.ora
inithsodbc.ora
from the SERVER this database link is created on and I'll take a look see.
can you tnsping this entry as well?
jortiz, May 19, 2004 - 11:57 am UTC
Hello there !!!
I Did what sivababu said at the begining and It worked ok immediatly !!!
I dind't have to do anything else !!
Reviewer: sivababu from INDIA
Hello TOM,
Generic Connectivity is not working properly after configured.
My Oracle 9i (personal edition ) IP is 192.168.181.21 ( Windows 98 )
Our Sqlserver 2000 Ip is 192.168.181.4 (windows 2000)
1. The odbc is created in System dsn. it is called hsodbc
2. The values from C:\oracle\ora91\hs\admin\inithsodbc.ora
HS_FDS_CONNECT_INFO = hsodbc
HS_FDS_TRACE_LEVEL = 0
3. The tnsnames.ora from C:\oracle\ora91\network\ADMIN\tnsnames.ora
hsodbc =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.181.21)(PORT=1521)) --> i tried
both IP.
(CONNECT_DATA=(SID=hsodbc))
(HS=OK)
)
siva =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.181.21)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = siva)
)
)
4. The listener.ora from C:\oracle\ora91\network\ADMIN\listener.ora
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
SID_LIST_LISTENER =
(SID_LIST=
(SID_DESC =
(GLOBAL_DBNAME = siva)
(ORACLE_HOME = c:\oracle\ora91)
(SID_NAME = siva)
)
(SID_DESC=
(SID_NAME=hsodbc)
(ORACLE_HOME=c:\oracle\ora91)
(PROGRAM=hsodbc)
)
)
5. The sqlnet.ora has this line.
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
6. If i will do the following steps i"m getting error.
SQL> connect scott/tiger@siva
Connected.
SQL> create database link hsodbc
2 CONNECT TO sa IDENTIFIED BY sa
3 using 'hsodbc';
Database link created.
SQL> select * from abc@hsodbc; --Here I got the answer from SQL Server
It was so good !
GET A RIGHT CONNECTION !
jortiz, May 19, 2004 - 11:57 am UTC
Hello there !!!
I Did what sivababu said at the begining and It worked ok immediatly !!!
I dind't have to do anything else !!
Reviewer: sivababu from INDIA
Hello TOM,
Generic Connectivity is not working properly after configured.
My Oracle 9i (personal edition ) IP is 192.168.181.21 ( Windows 98 )
Our Sqlserver 2000 Ip is 192.168.181.4 (windows 2000)
1. The odbc is created in System dsn. it is called hsodbc
2. The values from C:\oracle\ora91\hs\admin\inithsodbc.ora
HS_FDS_CONNECT_INFO = hsodbc
HS_FDS_TRACE_LEVEL = 0
3. The tnsnames.ora from C:\oracle\ora91\network\ADMIN\tnsnames.ora
hsodbc =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.181.21)(PORT=1521)) --> i tried
both IP.
(CONNECT_DATA=(SID=hsodbc))
(HS=OK)
)
siva =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.181.21)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = siva)
)
)
4. The listener.ora from C:\oracle\ora91\network\ADMIN\listener.ora
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
SID_LIST_LISTENER =
(SID_LIST=
(SID_DESC =
(GLOBAL_DBNAME = siva)
(ORACLE_HOME = c:\oracle\ora91)
(SID_NAME = siva)
)
(SID_DESC=
(SID_NAME=hsodbc)
(ORACLE_HOME=c:\oracle\ora91)
(PROGRAM=hsodbc)
)
)
5. The sqlnet.ora has this line.
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
6. If i will do the following steps i"m getting error.
SQL> connect scott/tiger@siva
Connected.
SQL> create database link hsodbc
2 CONNECT TO sa IDENTIFIED BY sa
3 using 'hsodbc';
Database link created.
SQL> select * from abc@hsodbc; --Here I got the answer from SQL Server
It was so good !
OK
A reader, May 19, 2004 - 12:10 pm UTC
I can send you my scripts and what I did step by step to help you with this !!
write to jortiz@avansoft.com only if it's neccesary !
generic connectivity vs oracle gateways
amit, June 29, 2006 - 11:09 am UTC
your (not ur) help always appreciated.
Now, We need to transfer data from oracle to sql server on daily basis(one table).
q1) I know we have two methods of doing that
1) generic connectivity
2) oracle gateways
Now my query is when to use which method ? Are there any pros and cons of using one over other ?
q2) after using above method can I do it from inside oracle database or I have to go sql server to get data ?
sql-oracle > insert into table_of_sqlserver@db_link select * from oracle_table;
q3) is Replication possible here !
Thanks always
Amit
June 29, 2006 - 11:11 am UTC
q1) if generic connectivity meets your needs - it would be by far the easiest approach.
q2) you would be able to query sqlserver from oracle, our gateways/generic connectivity stuff reach out FROM Oracle to Other database.
q3) but not practical, you could technically create a full refresh materialized view - but not changes only refreshes.
Generic Connectivity or Gateway?
Jon, June 29, 2006 - 12:35 pm UTC
equation
amit, July 03, 2006 - 9:37 am UTC
<quote>
if generic connectivity meets your needs - it would be by far the easiest approach.
</quote>
SO, if the following equation is right
gateways = generic connectivity + few more things.
what are these few more things ? (except cost )
July 07, 2006 - 6:53 pm UTC
native drivers in some cases... providing more functionality.
functionality
amit, July 11, 2006 - 9:36 am UTC
<quote>
native drivers in some cases... providing more functionality.
</quote>
what extra functionality ?
Thanks