I have been following several sources to set up an Oracle DBlink to select from a SQL Server database.
Oracle Version is 220.127.116.11.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_NAME=mySqlServer) <-- the name of the ODBC connection
(ORACLE_HOME = D:\app\oracle\product\12.1.0\dbhome_1)
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:
(CONNECT_DATA=(SID=mySqlServer)) <-- the name of the ODBC connection
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?