This discussion is archived
3 Replies Latest reply: Nov 30, 2012 10:24 AM by 723623 RSS

Oracle TO MSSQLServer vi ODBC Hangs

723623 Newbie
Currently Being Moderated
Hi everyone:

I'm trying to connect from an Oracle 11.2.0.3 on Solaris 10 (64 bit SPARC) to a MS SQL server 2000 using openlink odbc drivers 6.1 for SQL Server.

We can do a tnsping to the remore database and it works, but when we try to query any table from SQL Server connections hangs always in the same step.



Oracle Corporation --- MONDAY NOV 26 2012 11:20:26.750


Heterogeneous Agent Release
11.2.0.2.0




Oracle Corporation --- MONDAY NOV 26 2012 11:20:26.749

Version 11.2.0.2.0

Entered hgogprd
HOSGIP for "HS_FDS_TRACE_LEVEL" returned "DEBUG"
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"
setting HS_NLS_NCHAR to default of "AL32UTF8"
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"
HOSGIP returned value of "1" for HS_FDS_FETCH_ROWS
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"
HOSGIP returned value of "FALSE" for HS_FDS_SUPPORT_STATISTICS
Parameter HS_FDS_QUOTE_IDENTIFIER is not set
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"
setting HS_FDS_DELAYED_OPEN to default of "TRUE"
setting HS_FDS_WORKAROUNDS to default of "0"
Exiting hgosdip, rc=0
ORACLE_SID is "CardHolders"
Product-Info:
Port Rls/Upd:2/0 PrdStat:0
Agent:Oracle Database Gateway for ODBC
Facility:hsa
Class:ODBC, ClassVsn:11.2.0.2.0_0008, Instance:CardHolders
Exiting hgogprd, rc=0
Entered hgoinit
HOCXU_COMP_CSET=1
HOCXU_DRV_CSET=31
HOCXU_DRV_NCHAR=873
HOCXU_DB_CSET=873
HS_LANGUAGE is AMERICAN_AMERICA.WE8ISO8859P1
rc=2147476520 attempting to get LANG environment variable.
HOCXU_SEM_VER=112000
Entered hgolofn at 2012/11/26-11:20:26
HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/apex01/oraapex/ODBC_64/lib/sql_mt_lt.so"
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a170e68
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a1724b0
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a18db90
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a172520
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a18dce0
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a17dca0
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a191240
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a182600
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a182cc0
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a183270
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a183988
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a182d10
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a17e190
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a184ec8
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a1876d8
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a187928
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a18dc30
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a18dc88
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a1887a0
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a17e130
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a17dad8
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a173ba0
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a173ef0
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a174560
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a17dea0
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a156d60
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a17f070
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a183048
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a1760a0
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a17c2f0
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a17db78
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a184390
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a184ac0
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a1873f8
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a1906d8
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a191038
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a187dc8
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a188048
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a1886c8
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a1883a0
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a176040
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a190738
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a17c368
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a18e280
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Entered hgolofns at 2012/11/26-11:20:26
symbol_peflctx=0x7a190b28
hoaerr:0
Exiting hgolofns at 2012/11/26-11:20:26
Exiting hgolofn, rc=0 at 2012/11/26-11:20:26
HOSGIP for "HS_OPEN_CURSORS" returned "50"
HOSGIP for "HS_FDS_FETCH_ROWS" returned "1"
HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"
HOSGIP for "HS_NLS_NUMERIC_CHARACTER" returned ".,"
HOSGIP for "HS_KEEP_REMOTE_COLUMN_SIZE" returned "OFF"
HOSGIP for "HS_FDS_DELAYED_OPEN" returned "TRUE"
HOSGIP for "HS_FDS_WORKAROUNDS" returned "0"
HOSGIP for "HS_FDS_MBCS_TO_GRAPHIC" returned "FALSE"
HOSGIP for "HS_FDS_GRAPHIC_TO_MBCS" returned "FALSE"
Invalid value of 64 given for HS_FDS_SQLLEN_INTERPRETATION
treat_SQLLEN_as_compiled = 1
Exiting hgoinit, rc=0 at 2012/11/26-11:20:26
Entered hgolgon at 2012/11/26-11:20:26
reco:0, name:SA, tflag:0
Entered hgosuec at 2012/11/26-11:20:26
Exiting hgosuec, rc=0 at 2012/11/26-11:20:26
HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"
HOSGIP for "HS_FDS_TRANSACTION_LOG" returned "HS_TRANSACTION_LOG"
HOSGIP for "HS_FDS_TIMESTAMP_MAPPING" returned "DATE"
HOSGIP for "HS_FDS_DATE_MAPPING" returned "DATE"
HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"
HOSGIP for "HS_FDS_RESULTSET_SUPPORT" returned "FALSE"
HOSGIP for "HS_FDS_RSET_RETURN_ROWCOUNT" returned "FALSE"
HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"
HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"
using SA as default value for "HS_FDS_DEFAULT_OWNER"
HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
Entered hgocont at 2012/11/26-11:20:26
HS_FDS_CONNECT_INFO = "CardHolders"
RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
Entered hgogenconstr at 2012/11/26-11:20:26
dsn:CardHolders, name:SA
optn:
Entered hgocip at 2012/11/26-11:20:26
dsn:CardHolders
Exiting hgocip, rc=0 at 2012/11/26-11:20:26
Exiting hgogenconstr, rc=0 at 2012/11/26-11:20:26


We configured the odbc.ini, initDB.ora , listener.ora an tnsnmaes.ora as follows.

[ODBC Data Sources]
CardHolders = OpenLink SQL Server Lite Driver (multi threaded)

*** odbc.ini
[CardHolders]
Driver = /apex01/oraapex/ODBC_64/lib/sql_mt_lt.so
ServerType = SQLServer 2000
Username = SA
Password = Passw0rd
Database = CardHolders
Options = -H 192.168.40.15 -P 1433 -V 9
FetchBufferSize = 99
ReadOnly = Yes
DeferLongFetch =
JetFix = No
Description = Sample SQLServer 2000 Lite Connection
MaxRows =
NoRowSetSizeLimit = No
InitialSQL =
NoAutoCommit = No
NoLoginBox =
SqlDbmsName =
QuotedId = Yes
AnsiNPW = No

*** initCardHolders.ora
HS_FDS_CONNECT_INFO=CardHolders
HS_FDS_TRACE_LEVEL = DEBUG
HS_FDS_TRACE_FILE_NAME = CardHolders.trc
HS_FDS_SHAREABLE_NAME = /apex01/oraapex/ODBC_64/lib/sql_mt_lt.so
HS_FDS_FETCH_ROWS=1
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
HS_FDS_SUPPORT_STATISTICS=FALSE


*** listener.ora (added)
(SID_DESC =
(SID_NAME = CardHolders)
(ORACLE_HOME= /apex01/oraapex/apexdb/11.2.0.2)
(PROGRAM = dg4odbc)
(ENVS=LD_LIBRARY_PATH=/apex01/oraapex/ODBC_64/lib:$ORACLE_HOME/lib)
)

*** tnsnames.ora (added)
CardHolders =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1530))
(CONNECT_DATA=(SERVICE_NAME=CardHolders))
(HS=OK)
)


Thanks in advance, any help would be appreciated.
Regards!!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points