We help IT Professionals succeed at work.

Use Oracle DBlink to select data from SQL Server

I have been following several sources to set up an Oracle DBlink to select from a SQL Server database.

Oracle Version is 12.1.0.2.0 Standard Edition in Windows 2012 Datacenter R2 64-bit

SQL Server version is:  SQL Server 2016 (SP2-GDR) (KB4505220) - 13.0.5101.9 (X64)   Jun 15 2019 23:15:58   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2012 R2 Datacenter 6.3 <X64>

The ODBC set up Tests successfully.  I've named it "mysqlServer"

My Oracle Server's name is:  MyOraServer

Here's what I've done:

Step 1:  Added the following entries in the %Grid_Home%\network\admin\listener.ora

SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=mySqlServer)  <-- the name of the ODBC connection
         (ORACLE_HOME = D:\app\oracle\product\12.1.0\dbhome_1)
         (PROGRAM=hsodbc)
       )
      )


Step 2:  Created an init file in the %ORACLE_DBHOME%\hs\admin folder called "inithsMySqlServer.ora" with the following content:

HS_FDS_CONNECT_INFO = mySqlServer    <-- the name of the ODBC connection
HS_FDS_TRACE_LEVEL = off



Step 3:   Made the following entry in the %ORACLE_DBHOME%\network\admin\tnsnames.ora file:

SQLServerDB  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=MyOraServer)(PORT=1521))
    (CONNECT_DATA=(SID=mySqlServer))  <-- the name of the ODBC connection
    (HS=OK)
  )


Step 4:  Created a DBLink in Oracle:

CREATE PUBLIC DATABASE LINK SQLServerDB CONNECT TO "myusername" IDENTIFIED by "mypassword" USING 'SQLServerDB';


The user "myusername" exists in SQL Server.  I've logged into it (in SQL server) and was able to select the appropriate data.


When I try a select from a SQL table I have been granted access, I get the following error:

Oracle SQL>  select count(*) from mytable@SQLServerDB
                                    *
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 SQLServerDB

What am I doing wrong?
Comment
Watch Question

SILVER EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

I've not done much with Heterogeneous Services but it looks like you might be using the older drivers:     (PROGRAM=hsodbc)

https://oracle-base.com/articles/misc/heterogeneous-services-generic-connectivity


Try   DG4ODBC



Julie KurpaSr. Systems Programmer

Author

Commented:
Thanks slightwv.  

So I'm changing the listener entry to this:

SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=mySqlServer)  <-- the name of the ODBC connection
         (ORACLE_HOME = D:\app\oracle\product\12.1.0\dbhome_1)
         (PROGRAM=dg4odbc)
       )
      )



And now how a %ORACLE_DBHOME%\hs\admin\initdg4odbc.ora file with the entries:

HS_FDS_CONNECT_INFO = mySqlServer    <-- the name of the ODBC connection
HS_FDS_TRACE_LEVEL = off


I've restarted my Listener and am still receiving the same error:

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 mySQLServer
SILVER EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

See if there are any additional messages in the listener.log.

Julie KurpaSr. Systems Programmer

Author

Commented:
I see an error:

14-FEB-2020 10:55:00 * (CONNECT_DATA=(SID=mySqlServer)(CID=(PROGRAM=)(HOST=MyOraServer)(USER=administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=x.x.x.x)(PORT=55442)) * establish * mySqlServer * 12518
TNS-12518: TNS:listener could not hand off client connection
 TNS-12560: TNS:protocol adapter error
  TNS-00530: Protocol adapter error
   64-bit Windows Error: 2: No such file or directory
SILVER EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Julie KurpaSr. Systems Programmer

Author

Commented:
No.  Just one oracle home (db_home) and a separate grid home.  The listener is in the Grid Home.   The tnsnames.ora is in the DB_home.


Doing a listener status shows this for the SQL server entry:

Service "mySqlServer" has 1 instance(s).
  Instance "mySqlServer", status UNKNOWN, has 1 handler(s) for this service...
SILVER EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

Check out the Support note I provided.  It seems related.

Julie KurpaSr. Systems Programmer

Author

Commented:
I added the path of the dg4odbc file to the listener PROGRAM entry.   I'm getting a new error!  :D

select count(*) from mytable@SQLServerDB
                                    *
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-28541: Error in HS init file on line 39.
ORA-02063: preceding 2 lines from SQLSERVERDB
SILVER EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

I'm not a HS Expert.  I'm having to look up the same things you are likely reading.


Hopefully an Expert more familiar with HS will be along later.

Julie KurpaSr. Systems Programmer

Author

Commented:
I'm assuming it's talking about the %ORACLE_DBHOME%\hs\admin\initdg4odbc.ora file.  But I don't have a line 39.
Julie KurpaSr. Systems Programmer

Author

Commented:
I'm trying to do this without installing Oracle Gateway.    The reason is that I read somewhere that if you have any patches applied to your oracle installation, installing Gateway may overlay patched files.  

I tried installing Gateway and giving it a different path than my oracle home but it gave me an error.  Said something about needing to specify an existing oracle path.   Got nervous and backed out.
Julie KurpaSr. Systems Programmer

Author

Commented:
Changed the name of the init file in the %ORACLE_DBHOME%\hs\admin to "initmySqlServer.ora".

Now getting yet a new error.   Making progress!!

ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture
mismatch between the Driver and Application {IM014}
ORA-02063: preceding 2 lines from SQLSERVERDB
Julie KurpaSr. Systems Programmer

Author

Commented:
We did it!!!

I deleted the ODBC configuration I had used (syswow64\odbcad32.exe) and used the 64-bit ODBC instead and it worked!!!!
Julie KurpaSr. Systems Programmer

Author

Commented:
Very helpful as usual!    Thanks Slighwv!