Home » RDBMS Server » Networking and Gateways » Connecting to MSSQL Server (11.2.0.4 (Oracle Linux 6))
Connecting to MSSQL Server [message #651811] Wed, 25 May 2016 09:46 Go to next message
tony_soprano786
Messages: 5
Registered: November 2015
Junior Member
Hi

I want to be able to query data in my 2008 MSSQL server, from my Oracle 11gR2 instance. I'm trying to achieve this by using FreeTDS/unixODBC

I've got as far as installing/configuring FreeTDS and unixODBC in my Oracle environment, which has been successful; I'm able to connect to my MSSQL Server using isql.

Now, I'm a little lost with installing/configuring things from Oracles perspective. I understand that I need to use Oracle Heterogeneous services? is there much left to configure/set-up to get this working?
Re: Connecting to MSSQL Server [message #651812 is a reply to message #651811] Wed, 25 May 2016 09:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
https://www.google.com/webhp?hl=en&tab=ww#hl=en&q=oracle+connect+to+mssql+server

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Re: Connecting to MSSQL Server [message #651850 is a reply to message #651811] Thu, 26 May 2016 06:50 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
I don't know about "FreeTDS/unixODBC". It seems like you've bought stuff you don't need.

All you need is the Oracle Transparent Gateway for ODBC (free), and the MS supplied (free) odbc driver for MSSQL. I have multiple oracle databases on multiple hosts connecting to multilple MSSQL databases on multiple hosts. I simply installed the OTG (Oracle gateway) on one of the MS boxes (the gateway is just a switchboard) and configured the connections as needed.

I wrote this article as an overview of how the parts fit together: http://edstevensdba.com/oracle-heterogeneous-communication/oracle-transparent-gateway/

Also, there is a link to a very helpful Gateway configuration utility on MOS at Doc ID 1351616.1
It is a little Java app that takes user input for key info, then generates files with the neccessary CREATE LINK, tnsnames.ora, listener.ora, etc.
Re: Connecting to MSSQL Server [message #651851 is a reply to message #651850] Thu, 26 May 2016 07:12 Go to previous messageGo to next message
tony_soprano786
Messages: 5
Registered: November 2015
Junior Member
Appreciate the reply. Nice article too.

The tools I've mentioned that I've used are all free, and looking at your article, they work fundamentally the same as the mechanisms you mention.

I have made progress since I wrote the original post, I can hit my sql server via a db link in Oracle, I see the connection coming in via the sql server logs, however, I'm hitting an error in my sql server:

'Length specified in network packet payload did not match number of bytes read; the connection has been closed'

Just looking into this now. Don't know if you're familiar with the error?
Re: Connecting to MSSQL Server [message #651883 is a reply to message #651851] Thu, 26 May 2016 13:35 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
tony_soprano786 wrote on Thu, 26 May 2016 07:12
Appreciate the reply. Nice article too.

The tools I've mentioned that I've used are all free, and looking at your article, they work fundamentally the same as the mechanisms you mention.



"Fundamentally the same" is not the "same" as "same". Why do you need/want any third-party components at all? MS already supplies the odbc driver for MSSQL. Who better to supply and odbc driver for ANY database, than the vendor of that database? And the other side of the equation is Oracle Transparent Gateway. Who better to supply the gateway from Oracle than Oracle? Where, exactly, does this third-party FreeTDS fit in?

I've never seen the error you report, but then I've never tried to connect Oracle to MSSQL with unnecessary third-party products.
Previous Topic: What should I do so that the listener is reading the configuration from the new ORACLE Installation
Next Topic: SSIS package creation
Goto Forum:
  


Current Time: Mon Mar 18 22:32:17 CDT 2024