Home » RDBMS Server » Networking and Gateways » Connecting Oracle to SQL Server via database link
Connecting Oracle to SQL Server via database link [message #508826] Tue, 24 May 2011 10:20 Go to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Connecting Oracle to SQL Server via database link

Hi all,

I am trying to connect from Oracle 10G to a SQL Server database. I have looked at the manual and i will admit that i am finding the documentation quite difficult to follow. There seems to be several options to use but none of the documentation describe how each option works.

As an example, i have been given the following information on the database i need to connect to (i.e. the SQL Server database)

- Username
- Password
- Database Name [lets assume the database name is data_extract]

To connect the above i made the following changes

$ORACLE_HOME/hs/admin/inithsodbc.ora
---------------------------------------

HS_FDS_CONNECT_INFO = data_extract
HS_FDS_TRACE_LEVEL = 0


$ORACLE_HOME/network/admin/tnsnames.ora
---------------------------------------
sqlserver.db =
        (DESCRIPTION =
                (ADDRESS = (protocol=tcp)(host=10.10.10.10)(port=49400))
                        (connect_data = (sid=data_extract))
                        (hs=ok)
        )



$ORACLE_HOME/network/admin/listener.ora
---------------------------------------

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = merlin)(PORT = 1525))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u/app/oracle/product/10.2.0/db)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ccsbill.syntegra.bt.co.uk)
      (ORACLE_HOME = /u/app/oracle/product/10.2.0/db)
      (SID_NAME = billdb)
    )
    (SID_DESC =
        (SID_NAME = Avante_Import)
        (ORACLE_HOME = /u/app/oracle/product/10.2.0/db)
        (program = hsodbc)
    )
  )


Note: In listener.ora, i only added the last SID_DESC entry.

I then went on and created the database link as shown below

create database link sqlservdb using 'sqlserver.db';


When i try to access a table i get the following error

sqlplus> select * from TESTTABLE@sqlservdb;
select * from TESTTABLE@sqlservdb
                           *
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from ORASQLSERVER



A couple of things i am not sure of

- Where do i specify the username and password to access the sql server from the oracle db to the sql server db
- Having read around on the internet, i can see some people refering to a DSN datasource. I am told that the information that i have is all i need. Is this correct or do i need something else?
- The sid i specified in inithsodbc.ora and tnsnames.ora is actually the sql server database. Is this correct?

Any help with the above will be appreciated. Thanks






Re: Connecting Oracle to SQL Server via database link [message #508832 is a reply to message #508826] Tue, 24 May 2011 10:26 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
you post using V10 but error states "ORA-28545: error diagnosed by Net8 when connecting to an agent"

Is V8 Oracle installed on this DB Server?
Re: Connecting Oracle to SQL Server via database link [message #508834 is a reply to message #508832] Tue, 24 May 2011 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The error message did not change, even in 11.2. Oracle does not update its message files so often. Sad

Regards
Michel
Re: Connecting Oracle to SQL Server via database link [message #508853 is a reply to message #508834] Tue, 24 May 2011 11:13 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
I am using v10G

VERSION
-----------------
10.2.0.3.0
Re: Connecting Oracle to SQL Server via database link [message #508887 is a reply to message #508853] Tue, 24 May 2011 17:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
28545, 0000, "error diagnosed by Net8 when connecting to an agent"
// *Cause:   An attempt to call an external procedure or to issue SQL
//           to a non-Oracle system on a Heterogeneous Services database link
//           failed at connection initialization.  The error diagnosed
//           by Net8 NCR software is reported separately.
// *Action:  Refer to the Net8 NCRO error message.  If this isn't clear,
//           check connection administrative setup in tnsnames.ora
//           and listener.ora for the service associated with the
//           Heterogeneous Services database link being used, or with
//           'extproc_connection_data' for an external procedure call.
Re: Connecting Oracle to SQL Server via database link [message #508888 is a reply to message #508887] Tue, 24 May 2011 18:27 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The way I set it up under AIX a few years back:

- I installed Unixodbc and FreeTds to get an ODBC data source. (That would probably be simpler when you are on Windows since then you can just use the Microsoft SQLServer drivers and the Microsoft ODBC configurator).

Then it was basically:

- You set an HS_FDS_CONNECT_INFO parameter in the init<DataSource>.ora file in /hs/admin
- That name is the ODBC data source (on Unix configured in the odbc.ini file wherever you installed UnixODBC)
- In that config you tell it to use the FreeTDS driver or whatever commercial driver you choose.
- The username / password and stuff you specify in the ODBC driver configuration (freetds.conf in case of FreeTDS)

If I remember correctly there was a lot of trial and error involved, and quite a few hacking to get the drivers compiled. At least there are two executables (osql for FreeTDS and isql for OpenODBC) where you can check if you can get a connection on THAT level.

I don't have that setup anymore, so I can't really look for details.
Re: Connecting Oracle to SQL Server via database link [message #508938 is a reply to message #508888] Wed, 25 May 2011 05:53 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
I am a bit confused. Does the sqlserver.db entry i have shown above have to point to the SQL Server database running on Windows or the Local Unix box running the database. ?
Re: Connecting Oracle to SQL Server via database link [message #508941 is a reply to message #508938] Wed, 25 May 2011 06:16 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Which sqlserver.db entry?

The entry you do in the "create database link" should point to the an entry in the tnsnames.ora used.

From an old backup I was able to re-construction the "configuration chain" through Oracle, ODBC and the FreeTDS driver:

The TNSNAMES.ora entry you specify in the database link points to the listner. Entry in tnsnames.ora I found in a backup (It was a link to a CMS database):

CMS=(DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)
             (HOST=localhost)
             (PORT=1531)
           )
(CONNECT_DATA=(SID=CMS))
              (HS=OK)
        )


The entry I had in listener.ora. (Due to some reason the LD_LIBRARY_PATH was needed, since some part of the driver was a 32 bit library):

LISTENERhs =
 (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1531))
      (ADDRESS=(PROTOCOL=ipc)(KEY=CMS))
 )

SID_LIST_LISTENERhs=
  (SID_LIST=
       (SID_DESC=
         (SID_NAME=CMS)
         (ORACLE_HOME = /pfk/app/oracle/product/10.2.0)
         (PROGRAM=hsodbc)
         (ENVS=LD_LIBRARY_PATH=/usr/local/lib:/pfk/app/oracle/product/10.2.0/lib32)
      )
)


the initCMS.ora file in /hs/admin, which also had the info which odbc library to use :

HS_FDS_CONNECT_INFO = CMS
HS_FDS_SHAREABLE_NAME = /usr/local/lib/libodbc.so
HS_LANGUAGE=American_America.we8iso8859p1

# ODBC specific environment variables
set ODBCINI=/usr/local/etc/odbc.ini


Then the entry in the odbc.ini, at this point it is specified which databse on the SQL Server is to be used :
[CMS]
Driver = TDS
Description = Document Management System
Servername = srvCMS
Database = cms_db


which specified the entry srvCMS, which was the server entry in freetds.conf:

[srvCMS]
        host = 192.168.10.5
        port = 1433
        tds version = 7.0


"localhost" was the Oracle machine, 192.168.10.5 was the SQLServer.

The username and password is specified while creating the database link. That has to be an "internal" SQLServer user as far as I remember, login with a Windows user name and password was not possible.
Re: Connecting Oracle to SQL Server via database link [message #508947 is a reply to message #508941] Wed, 25 May 2011 06:28 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Thanks that is very helpfull Thomas. I have not installed any driver yet. I thought that with Oracle 10G the Oracle hsodbc driver can be used as it is. Do i HAVE to install a driver for this to work?

Also, in your tnsentry shown below

CMS=(DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)
             (HOST=localhost)
             (PORT=1531)
           )
(CONNECT_DATA=(SID=CMS))
              (HS=OK)
        )



Is CMS the SID for your local database?
Re: Connecting Oracle to SQL Server via database link [message #508972 is a reply to message #508947] Wed, 25 May 2011 07:36 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
"CMS" is basically only the TNSNAME that the listener is using to connect to know which of the Heterogenous Service config files , (initCMS.ora in this case) it has to use in ../hs/admin/.

Basically, the way I understand it, the hsodbc driver can talk ODBC. SQLServer *cant* talk ODBC directly, so you need an SQLServer(or Sybase, since the TDS protocoll is practically identical) driver that allows you to do do things over ODBC.

Of course Microsoft doesn't offer Unix drivers, so I went with freeTDS and unixODBC. Another option would be the Easysoft Unix drivers for SQLServer. A lot of their documentation was helpfull, even if I didn't end up using their drivers.


Re: Connecting Oracle to SQL Server via database link [message #509265 is a reply to message #508972] Thu, 26 May 2011 13:37 Go to previous message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Do you have any enough information here
Previous Topic: ORA-12154
Next Topic: HSODBC
Goto Forum:
  


Current Time: Thu Mar 28 05:51:46 CDT 2024