| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Oracle Generic Connectivity talk to SQL server with free ODBC
Hi All,
We have Oracle 10g in HP-UX. We need connect to SQL server 2000 in
Oracle environment. I download ODBC drvier from datadirect and
installed it in HP-UX. Everthing looked fine. The problem is we had to
pay lots of money for ODBC license fee.
I found an article at asktom and it's said
free to connect to non-oracle database. It needn't install any ODBC
driver in Unix server.
http://asktom.oracle.com/pls/ask/f?p=4950:8:11639420469415491754::NO::F4950_P8_D
ISPLAYID,F4950_P8_CRITERIA:4406709207206
1. Open the ODBC Data Source Administrator
[ Start -> Programs -> Administrative Tools -> Data Sources (ODBC) ]
Click on the tab page labeled "System DSN"
Click on the button labeled "Add"
Select the Excel driver, and press the button marked "Finish"
In the box labeled 'Data Source Name', enter the word 'SPONGE'.
Click on the button labeled 'Select Workbook'
Select the excel workbook you saved earlier.
Click OK, and exit the ODBC Administrator
This has created a Data Source Name (called 'SPONGE') that points to
your excel
workbook.
2. First, we need to tell Hetrogeneous Services about SPONGE.
The HS initialisation files are stored in ORACLE_HOME\hs\admin. In
there, you
will find a file named 'inithsodbc.ora'. Make a copy of it called
'initFRUIT.ora', and edit the file.
HS_FDS_CONNECT_INFO = SPONGE
HS_FDS_TRACE_LEVEL = 0
Save the file.
This has created a link between the SID 'FRUIT' to the DSN 'SPONGE'.
Now edit your LISTENER.ORA file. In the SID_LIST, add the following
entry :-
(SID_DESC=
(SID_NAME=FRUIT)
(ORACLE_HOME=C:\Oracle\Ora9i) # change this to YOUR oracle home
(PROGRAM=hsodbc)
)
This instructs the listener that to service this sid, use 'hsodbc'.
You'll
need
to stop and start the listener to get it to pick up the changes, or
perhaps you
just need to use the 'reload' option - old habits die hard.
Next we need to define a service, so that NET8 knows how to connect to
that
sid.
Add the following entry to your TNSNAMES.ORA on your database server.
JELLY = # you may need to add a domain name suffix - ask your DBA
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=
(PROTO =TCP) # edit to point to your LISTENER
(HOST=localhost) # edit to point to your LISTENER
(PORT=1521) # edit to point to your LISTENER
)
)
(CONNECT_DATA=(SID=FRUIT))
(HS=OK)
)
Finally, we can log in to oracle. Create a database link to refer to
the JELLY
service. For excel, we seem to need to specify the Windows 2000 account
name
('tangt' on machine 'home' becomes "HOME\tangt") and password :-
CREATE DATABASE LINK custard
CONNECT TO "home\tangt"
IDENTIFIED BY taktangspassword
USING 'jelly';
Now try this :-
SELECT table_name
FROM all_tables_at_custard;
TABLE_NAME
Sheet1$ Sheet2$ Sheet3$
---------------------------Finished article-----------------But I got an error when I ran select statement. ERROR at line 1:
 |
 |