OraFAQ Forum: Networking and Gateways » Connecting to MSSQL Server

Home » RDBMS Server » Networking and Gateways » Connecting to MSSQL Server (11.2.0.4 (Oracle Linux 6))

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Connecting to MSSQL Server [message #651811]

Wed, 25 May 2016 09:46

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?

Report message to a moderator

Re: Connecting to MSSQL Server [message #651812 is a reply to message #651811]

Wed, 25 May 2016 09:49

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.

Report message to a moderator

Re: Connecting to MSSQL Server [message #651850 is a reply to message #651811]

Thu, 26 May 2016 06:50

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.

Report message to a moderator

Re: Connecting to MSSQL Server [message #651851 is a reply to message #651850]

Thu, 26 May 2016 07:12

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?

Report message to a moderator

Re: Connecting to MSSQL Server [message #651883 is a reply to message #651851]

Thu, 26 May 2016 13:35

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.

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 01 23:01:19 CST 2025