asked on

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?

slightwv (䄆 Netminder)

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 Kurpa

ASKER

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

slightwv (䄆 Netminder)

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

Julie Kurpa

ASKER

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

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Julie Kurpa

ASKER

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...

slightwv (䄆 Netminder)

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

Julie Kurpa

ASKER

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

slightwv (䄆 Netminder)

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 Kurpa

ASKER

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

Julie Kurpa

ASKER

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 Kurpa

ASKER

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 Kurpa

ASKER

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 Kurpa

ASKER

Very helpful as usual! Thanks Slighwv!