Forum Stats

  • 3,872,067 Users
  • 2,266,376 Discussions
  • 7,911,043 Comments

Discussions

Connect Oracle 12c to MS SQL 2017

Hi,

I am having a problem with the connection of an oracle database to ms sql database.

The way I'm doing the connection is with Heterogeneous Services, through dg4odbc.

The OS is Oracle Linux 7.6 x86-64 and Oracle Database 12c 12.1.0.2.0

I have been trying for 2 days to connect the sql db to the oracle db. I cannot figure out exactly what the problem is. Any help would be appreciated.

Also I have ODBC 17 and 18 installed. I'm not sure if that creates a problem or not.

This is the error:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from LBLDB
28500. 00000 - "connection from ORACLE to a non-Oracle system returned this message:"
*Cause:  The cause is explained in the forwarded message.
*Action:  See the non-Oracle system's documentation of the forwarded
      message.


ODBC.ini

[LBLDB]
Description = LBLDB
Driver = /usr/lib64/libmsodbcsql-17.so
Server = "sql server FQDN"
Port = 1433
Database = LBLDb

initLBLDB.ora

# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = LBLDB
HS_FDS_TRACE_LEVEL = 255
HS_FDS_SHAREABLE_NAME = /usr/lib64/libmsodbcsql-17.so
HS_FDS_TRACE_FILE_NAME=/tmp/ora_hs_trace.log
HS_NLS_NCHAR = UCS2
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#

TNSNAMES.ora

# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LBLDB =
 (DESCRIPTION =
  (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = "oracle FQDN")(PORT = 1521))
  )
  (CONNECT_DATA =
   (SID = LBLDB)
  )
  (HS = OK)
 )

LISTENER.ora

LISTENER =
 (DESCRIPTION_LIST =
  (DESCRIPTION =
   (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL=TCP)(HOST="oracle server name")(PORT=1521))
   )
   (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL=IPC)(KEY=EXTPROC0))
   )
  )
 )

SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
   (PROGRAM=dg4odbc)
   (SID_NAME=LBLDB)
   (ORACLE_HOME=/home/oracle/app/oracle/product/12.1.0/dbhome_1)
   (ENVS=LD_LIBRARY_PATH=/opt/microsoft/msodbcsql17/lib64)
  )
 )

USE_SID_AS_SERVICE_listener=on
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF

LBLDB_agt_log.trc

Oracle Corporation --- TUESDAY  OCT 04 2022 08:47:55.266

Heterogeneous Agent Release
12.1.0.2.0

Oracle Corporation --- TUESDAY  OCT 04 2022 08:47:55.266

  Version 12.1.0.2.0

Entered hgogprd
HOSGIP for "HS_FDS_TRACE_LEVEL" returned "255"
Entered hgosdip
 setting HS_OPEN_CURSORS to default of 50
 setting HS_FDS_RECOVERY_ACCOUNT to default of "RECOVER"
 setting HS_FDS_RECOVERY_PWD to default value
 setting HS_FDS_TRANSACTION_LOG to default of HS_TRANSACTION_LOG
 setting HS_IDLE_TIMEOUT to default of 0
 setting HS_FDS_TRANSACTION_ISOLATION to default of "READ_COMMITTED"
 HOSGIP returned value of "UCS2" for HS_NLS_NCHAR
 setting HS_FDS_TIMESTAMP_MAPPING to default of "DATE"
 setting HS_FDS_DATE_MAPPING to default of "DATE"
 setting HS_RPC_FETCH_REBLOCKING to default of "ON"
 setting HS_FDS_FETCH_ROWS to default of "100"
 setting HS_FDS_RESULTSET_SUPPORT to default of "FALSE"
 setting HS_FDS_RSET_RETURN_ROWCOUNT to default of "FALSE"
 setting HS_FDS_PROC_IS_FUNC to default of "FALSE"
 setting HS_FDS_MAP_NCHAR to default of "TRUE"
 setting HS_NLS_DATE_FORMAT to default of "YYYY-MM-DD HH24:MI:SS"
 setting HS_FDS_REPORT_REAL_AS_DOUBLE to default of "FALSE"
 setting HS_LONG_PIECE_TRANSFER_SIZE to default of "65536"
 setting HS_SQL_HANDLE_STMT_REUSE to default of "FALSE"
 setting HS_FDS_QUERY_DRIVER to default of "TRUE"
 setting HS_FDS_SUPPORT_STATISTICS to default of "FALSE"
 setting HS_FDS_QUOTE_IDENTIFIER to default of "TRUE"
 setting HS_KEEP_REMOTE_COLUMN_SIZE to default of "OFF"
 setting HS_FDS_GRAPHIC_TO_MBCS to default of "FALSE"
 setting HS_FDS_MBCS_TO_GRAPHIC to default of "FALSE"
Default value of 64 assumed for HS_FDS_SQLLEN_INTERPRETATION
 setting HS_CALL_NAME_ISP to "gtw$:SQLTables;gtw$:SQLColumns;gtw$:SQLPrimaryKeys;gtw$:SQLForeignKeys;gtw$:SQLProcedures;gtw$:SQLStatistics;gtw$:SQLGetInfo;gtw$:OPTTables;gtw$:OPTColumns;gtw$:OPTPrimaryKeys;gtw$:OPTForeignKeys;gtw$:OPTProcedures;gtw$:OPTStatistics"
 setting HS_FDS_DELAYED_OPEN to default of "TRUE"
 setting HS_FDS_WORKAROUNDS to default of "0"
 setting HS_WORKAROUNDS to default of "0"
Exiting hgosdip, rc=0
 ORACLE_SID is "LBLDB"
 Product-Info:
 Port Rls/Upd:2/0 PrdStat:0
 Agent:Oracle Database Gateway for ODBC
 Facility:hsa
 Class:ODBC, ClassVsn:12.1.0.2.0_0023, Instance:LBLDB
Exiting hgogprd, rc=0
hostmstr:     0:  HOA After hoagprd
hostmstr:     0:  HOA Before hoainit
Entered hgoinit
HOCXU_COMP_CSET=1
HOCXU_DRV_CSET=178
HOCXU_DRV_NCHAR=1000
HOCXU_DB_CSET=178
HS_LANGUAGE not specified
LANG=en_US.UTF-8
HOCXU_SEM_VER=121000
HOCXU_VC2_MAX=32767
HOCXU_RAW_MAX=32767
Entered hgolofn at 2022/10/04-08:47:55
HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/usr/lib64/libmsodbcsql-17.so"
Entered hgolofns at 2022/10/04-08:47:55
 libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLAllocHandle
 symbol_peflctx=0xec255930
 hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
 libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLBindCol
 symbol_peflctx=0xec27c360
 hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
 libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLBindParameter
 symbol_peflctx=0xec288eb0
 hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
 libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLCancel
 symbol_peflctx=0xec299330
 hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
 libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLDescribeParam
 symbol_peflctx=0xec289f80
 hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
 libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLDisconnect
 symbol_peflctx=0xec256bd0
 hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
 libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLEndTran
 symbol_peflctx=0xec2b3400
 hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
 libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLExecute
 symbol_peflctx=0xec22dfe0
 hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
 libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLFetch
 symbol_peflctx=0xec2a76e0
 hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
 libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLFreeHandle
 symbol_peflctx=0xec255d40
 hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
 libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLFreeStmt
 symbol_peflctx=0xec21c680
 hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
 libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLGetData
 symbol_peflctx=0xec26f280
 hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
 libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLGetEnvAttr
 symbol_peflctx=0xec299aa0
 hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
 libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLGetFunctions
 symbol_peflctx=0xec2964f0
 hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
 libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLMoreResults
 symbol_peflctx=0xec2a8080
 hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
 libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLNumResultCols
 symbol_peflctx=0xec27f6e0
 hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
 libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLParamData
 symbol_peflctx=0xec229360
 hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
 libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLPutData
 symbol_peflctx=0xec222c80
 hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
 libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLRowCount
 symbol_peflctx=0xec277580
 hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
 libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLSetEnvAttr
 symbol_peflctx=0xec299960
 hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
 libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLSetDescRec
 symbol_peflctx=0xec285de0
 hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
 libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLColAttribute
 peflerr=6521, libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLColAttribute
 hoaerr:28500
Exiting hgolofns at 2022/10/04-08:47:55
Failed to load ODBC library symbol: /usr/lib64/libmsodbcsql-17.so(SQLColAttribute)
Exiting hgolofn, rc=28500 at 2022/10/04-08:47:55
Exiting hgoinit, rc=28500 with error ptr FILE:hgoinit.c LINE:436 ID:Loading ODBC aray of function ptrs
hostmstr:     0:  HOA After hoainit
RPC Calling nscontrol(0), rc=0
hostmstr:     0: RPC Before Exit Agent
hostmstr:     0:  HOA Before hoaexit
Entered hgoexit
HS Gateway: NULL connection context at exit
Exiting hgoexit, rc=0 with error ptr FILE:hgoexit.c LINE:113 ID:Connection context
hostmstr:     0:  HOA After hoaexit
hostmstr:     0: RPC After Exit Agent
Entered horcrces_CleanupExtprocSession at 2022/10/04-08:47:55
Entered horcrpooe_PopOciEnv at 2022/10/04-08:47:55
Entered horcrfoe_FreeOciEnv at 2022/10/04-08:47:55
Exiting horcrfoe_FreeOciEnv at 2022/10/04-08:47:55
Entered horcrfse_FreeStackElt at 2022/10/04-08:47:55
Exiting horcrfse_FreeStackElt at 2022/10/04-08:47:55
Exiting horcrpooe_PopOciEnv at 2022/10/04-08:47:55
Exiting horcrces_CleanupExtprocSession at 2022/10/04-08:47:55


Tagged:

Answers