close
Welcome! Wikis are websites that everyone can build together. It's easy!

Location: The Official Oracle Wiki

Discussion: ODBC from oracle linux to Windows SQLServer

Keyword tags: odbc oracle oracle applications oracle linux

Watch

Anonymous  (Get credit for your thread)
jlmontoya

jlmontoya		 ODBC from oracle linux to Windows SQLServer
Dec 17 2007, 5:50 PM EST
I have a oracle 9i enterprise edition over windows 2k3 server running a data warehouse, the ETL process was designed in warehouse builder, the source of data is SQLServer database, the database access is through ODBC and oracle databases heterogeneous services.

we are moving to oracle 10g over linux enterprise,

How to make this odbc connection to sql server in a oracle 10g EE running over linux? 4  out of 14 found this valuable. Do you?    
naveednoor
naveednoor		 RE: ODBC from oracle linux to Windows SQLServer
Dec 17 2007, 5:57 PM EST
I think you need to install the libraries of ODBC on linux and then you need to connect with SQL server through these libraries and then reuse that with OWB 2  out of 6 found this valuable. Do you?    
jonah.harris
jonah.harris		 RE: ODBC from oracle linux to Windows SQLServer
Dec 17 2007, 11:57 PM EST
The cheap (free) and easy way would be to install unixODBC or iODBC on your Oracle 10g Linux system and use the FreeTDS ODBC driver to connect to SQL Server. This *does* work, I've tried it.
 1  out of 4 found this valuable. Do you?    
jlmontoya

jlmontoya		 RE: ODBC from oracle linux to Windows SQLServer
Dec 18 2007, 11:40 AM EST
"The cheap (free) and easy way would be to install unixODBC or iODBC on your Oracle 10g Linux system and use the FreeTDS ODBC driver to connect to SQL Server. This *does* work, I've tried it.
"
Thanks jonah, I was reading the documentation of the unixODBC, iODBC and FreeTDS
and sound very interesting, but I have two concerns:

The first is about FreeTDS and interoperability whit SQL Server 2005. the FAQ section no mention anything about SQL 2005. FreeTDS implements the protocol versions 4.2 7.0 y 8.0, you know what version support SQL 2005?

The second concern is Performance, my production enviroment normally loads from SQL 4M of records daily and up. is FreeTDS sufficiently robust to to support this load?
 1  out of 3 found this valuable. Do you?    
jonah.harris
jonah.harris		 RE: ODBC from oracle linux to Windows SQLServer
Dec 19 2007, 8:22 AM EST
Yes, FreeTDS supports SQL Server 2005. Though, the last time I checked, you had to force the client into 7.0 mode because Microsoft had changed the protocol and FreeTDS hadn't implemented the change yet. See http://lists.ibiblio.org/pipermail/freetds/2006q1/019824.html

As for the number of records, that's no problem at all. I easily copied over 40M rows from SQL Server to both Postgres and Oracle using FreeTDS.
 2  out of 4 found this valuable. Do you?    
oliver.gehlert
oliver.gehlert		 RE: ODBC from oracle linux to Windows SQLServer
Dec 19 2007, 8:25 AM EST
doublecheck that HSODBC is included with the database software you will install. AFAIK HSODBC is only included in the 32bit software install and is not working with 64bit Oracle. 1  out of 3 found this valuable. Do you?    
jlmontoya

jlmontoya		 RE: ODBC from oracle linux to Windows SQLServer
Dec 20 2007, 9:29 AM EST
"doublecheck that HSODBC is included with the database software you will install. AFAIK HSODBC is only included in the 32bit software install and is not working with 64bit Oracle. "
thansk, oliver

it is a problem, i was reviewing the database software and 10g 64bit Oracle does not support hsodbc.

In some FAQs found tricks like a "Copy the hsodbc executable from a 32 bit linux installation to $ORACLE_HOME/bin." but i don't belive that to work.
http://forums.oracle.com/forums/thread.jspa?threadID=381929&start=15&tstart=0

you know if exists other approaches to access MSSQLServer 2005 with out using hsodbc in Oracle Warehouse Builder over Oralce Enterprise Linux and with out too many effort of development?



 0  out of 1 found this valuable. Do you?    
oliver.gehlert
oliver.gehlert		 RE: ODBC from oracle linux to Windows SQLServer
Dec 20 2007, 10:20 AM EST
if you have an existing 32bit installation on another host, than use this listener for HSODBC.
Or you can use a virtual machine just for a 32bit installation with just a listener

Or you can buy the Oracle Transparent Gateway for SQL Server which has to be installed an the SQL Server server and which is just a stripped down version of the database software.
 2  out of 3 found this valuable. Do you?