PH27615: UNABLE TO INSERT MORE THAN 8000 BYTES USING NVARCHAR DATATYPE

APAR status

• Closed as program error.

Error description

• Unable to insert more than 8000 bytes into a MS SQL Server
  database using nvarchar datatype.
  
  The nvarchar column is set to max.
  
  The following error is found in the ITX Database trace file.
  
  Buffer not wide enough for data - expanding it
  Error in SQLExecute
  Message: [Microsoft][ODBC Driver 17 for SQL Server]String data,
  right truncation
  SQL State: 22001
  

Local fix

• ITXCQ - ITX00060465
  GK / GK
  Circumvention: None
  

Problem summary

• Users Affected:
  IBM Transformation Extender customers inserting data of size
  >8000 into nvarchar datatype of MS sql server table.
  
  Problem Description:
  Unable to insert more than 8000 bytes in nVarchar datatype into
  MS-SQL database on ITX 10. On ITX 9, we are using Data direct
  ODBC driver which allows to load data more than 8000 bytes in
  nvarchar datatype.
  But ITX 10 does not come with any ODBC drivers. So we need to
  use ODBC driver provided by Microsoft. And there is known issue
  with sqlbindparameter() as below :
  If the SQL data type passed in is a character type, ColumnSize
  is the size in characters (not bytes). If length of the data
  string in bytes is greater than 8000, you may observe a error as
  "String data, right truncation" error from the driver even if
  the value of ColumnSize is aligned with the size of the data
  type on the server.
  
  Platforms Affected:
  Windows Linux
  

Problem conclusion

• Fix the default precision for nVarchar datatype for UNIX-ODBC
  driver to SQL_SS_LENGTH_UNLIMITED.
  
  Applies to:
  10.0.0.0, 10.0.4.0
  
  Fixed in the next service packs and releases.
  
  To obtain the fix for this APAR:
  
  To see if the next service pack or product release is available,
  check the IBM Transformation Extender Release Notes page:
  https://www.ibm.com/support/docview.wss?uid=swg27008337
  
  If the service pack or product release is available, download it
  from Fix Central:
  http://www.ibm.com/support/fixcentral/
  
  If the service pack or product release is not available and you
  require the APAR fix immediately, request a Limited Availability
  Interim Fix (LAIF) by opening a case:
  https://www.ibm.com/mysupport/
  
  Prior to version 9.0.0, IBM Transformation Extender was called
  IBM WebSphere Transformation Extender.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  ITX

• Fixed component ID

  5724Q2300

Applicable component levels