Connection Oracle to SQL Server
From: Jim (jim_esti_at_hotmail.com)
Date: 04/06/04
- Next message: Sue Hoegemeier: "Re: Dynamically determine which server DTS is saved to"
- Previous message: Richard Hale: "Re: Backing up DTS packages?"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 6 Apr 2004 14:55:56 -0700
I have a large group of people using the SQL server. I
have another group of people that use Oracle - mainly for
payroll and such. Now the people using oracle want real
time access to the data that is on the SQL server.
I tried to create an Oracle database link to a SQL Server
database with no luck (see below). Has anyone had any
experince with this task? Or maybe a way that the SQL
server can update the oracle tables? I was working on a
Data Transformation Services package but realized that I
was looking at a few hundred tables. I really did not
want to create over a hundred Transfrom data tasks. Also
this would only fire on a scheduled bases and did not seem
to update the oracle tables rather it just seemed to
insert all the rows again. I need a real time way to
access the SQL server data from an oracle instance.
I am using Oracle8i Enterprise Edition Release 8.1.6.0.0
I am Using SQL Server (MSSQL7).
The Oracle server is one location, the SQL server is
another location and I am in a third location. I able to
login to the Oracle and SQL server from my location.
Here is what I have done so far:
1) From the Start menu, I clicked Settings > Control Panel
> Administrative Tools and select the Data Sources (ODBC)
icon.
2) Select the System DSN tab to display the system data
sources.
3) Click Add.
4) From the list of installed ODBC drivers, selected the
name of the driver that the data source will use - SQL
Server.
5) Click Finish.
6) Enter a name for the DSN, 'oracle_to_sql' and SQL
Server machine (MYSqlServer).
7) Continue clicking Next and answering the prompts until
I reached the end (click Finish).
8) I tested the connection and it was good.
9) added to the "network/admin/tnsnames.ora"
and "network/admin/listener.ora" files (on my local drive):
Tnsnames.ora
------------
hsodbc=
(description=
(address=(protocol=tcp)(host=MYSqlServer)
(port=1521))
(connect_data=(sid=hsodbc))
(hs=ok)
)
Listener.ora
------------
sid_list_listener=
(sid_list=
(sid_desc=
(sid_name=hsodbc)
(ORACLE_HOME = c:\orant)
(program= hsodbc)
)
)
10) Stopped/Started the Oracle listener
lsnrctl stop
lsnrctl start
11) In $ORACLE_HOME/hs/admin/inithsodbc.ora made sure I
added the statement below:
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = oracle_to_sql
12) Connect to the Oracle database and create a database
link to access the target database.
SQL> create database link hsodbc
SQL> connect to scott identified by tiger
SQL> using 'hsodbc';
--- Here I get a connection failed message.
So I tried to great a database link by:
create database link sqlserver connect to scott
identified by tiger
using 'ODBC:oracle_to_sql';
This appears to work but when I do the query:
select * from mytable@sqlserver;
I get the error message:
"ORA-06401: NetCMN: invalid driver designator"
I then edited the INIT.ORA file
and Set the global_names = false
But still get the "ORA-06401: NetCMN" error.
Any ideas on how I could accomplish my goal?
- Next message: Sue Hoegemeier: "Re: Dynamically determine which server DTS is saved to"
- Previous message: Richard Hale: "Re: Backing up DTS packages?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
