Hi,
I am using an Oracle Database Link with Oracle Database Free to connect to SQL Server via the Microsoft ODBC Driver 18 for SQL Server 16.0.1000.6. The connection encounters issues when handling columns of type **NVARCHAR(MAX)**, resulting in the following error:
[Microsoft][ODBC Driver 18 for SQL Server]String data, right truncation {01004}
The log file provides the following details:
Entered hgoftch2, cursor id 1 at 2024/11/25-20:55:53
hgoftch2, line 141: Printing hoada @ 00000247B5758828
MAX:2, ACTUAL:2, BRC:100, WHT=5 (SELECT_LIST)
hoadaMOD bit-values found (0x40:TREAT_AS_NCHAR,0x400:UNICODE_COLUMN)
DTY NULL-OK LEN MAXBUFLEN PR/SC CST IND MOD NAME
12 VARCHAR N 40 40 128/ 20 1000 0 440 PhoneNumber
12 VARCHAR Y 0 0 128/ 0 1000 0 440 InternalComments
Performing delayed open.
SQLBindCol: column 1, cdatatype: -8, bflsz: 42
SQLBindCol: column 2, cdatatype: -8, bflsz: 2
Entered hgopoer at 2024/11/25-20:55:53
hgopoer, line 240: got native error 0 and sqlstate 01004; message follows...
[Microsoft][ODBC Driver 18 for SQL Server]String data, right truncation {01004}
[Microsoft][ODBC Driver 18 for SQL Server]String data, right truncation {01004}
Exiting hgopoer, rc=0 at 2024/11/25-20:55:53
- The column
**PhoneNumber** (length 40) processes without errors.
- The problematic column
**InternalComments** has a length of 0 in the log (MAXBUFLEN is also 0).
- The binding (
SQLBindCol) shows that the buffer size for **InternalComments** is set to 2, which is insufficient for any meaningful content.
This issue appears to be related to how **NVARCHAR(MAX)** columns are handled:
- The ODBC driver seems to treat
**NVARCHAR(MAX)** as dynamic, but does not allocate sufficient buffer space.
- Oracle's Heterogeneous Services (HS) expects a maximum length but struggles with the dynamic nature of
**NVARCHAR(MAX)**.
- The error arises when attempting to fetch data from columns defined as dynamic (
MAX) or with lengths interpreted as 0.
init<SID>.ora
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = Test18
HS_FDS_TRACE_LEVEL = 4
HS_FDS_SUPPORT_STATISTICS = FALSE
HS_AUTOCOMMIT = TRUE
HS_FDS_TRANSACTION_MODEL = READ_ONLY
HS_LANGUAGE=AL32UTF8
HS_NLS_LENGTH_SEMANTICS=CHAR
listener.ora
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=Test18)
(ORACLE_HOME=C:\Datenbanken\Oracle\dbhomeFree)
(PROGRAM=dg4odbc)
Has anyone encountered similar issues with **NVARCHAR(MAX)** columns in SQL Server using ODBC Driver 18 and Oracle Database Links? Are there any alternative drivers, configuration options, or approaches to ensure proper handling of such columns?
Thank you in advance for your suggestions!
Best regards
Clemens