如何修复TPT脚本报错：[Microsoft][SQL Server Native Client 11.0]字符串数据右截断

问题详情 #

使用Teradata Parallel Transport（TPT）将SQL Server表数据抽取插入到Teradata同结构表，此前操作正常，本次命令行执行脚本触发以下错误：

命令行报错输出 #

TD_LOADER: connecting sessions
TD_LOADER: preparing target table(s)
TD_LOADER: entering DML Phase
TD_LOADER: entering Acquisition Phase
ODBC_READER: sending SELECT request
ODBC_READER: TPT17107: Retrieval error for row: 1
ODBC_READER: TPT17101: Fatal error received from ODBC driver:
STATE=01004, CODE=0,
MSG='[Microsoft][SQL Server Native Client 11.0]String data, right truncation'
ODBC_READER: TPT17101: Fatal error received from ODBC driver:
STATE=01004, CODE=0,type here

脚本生成的.out文件片段 #

TD_LOADER: connecting sessions
TD_LOADER: preparing target table(s)
TD_LOADER: entering DML Phase
TD_LOADER: entering Acquisition Phase
Job step 'load_the_data' uses the max row size 1024KB.
Job step 'load_the_data' uses the max Using Data Bytes: 1023991.
Job step 'load_the_data' allows data source containing rows that exceed the maximun 
length of 1024KB
Task(APPLY_1[0001]): checkpoint completed, status = Success
Task(SELECT_2[0001]): checkpoint completed, status = Success
ODBC_READER: sending SELECT request
ODBC_READER: TPT17107: Retrieval error for row: 1
ODBC_READER: TPT17101: Fatal error received from ODBC driver:
STATE=01004, CODE=0,
MSG='[Microsoft][SQL Server Native Client 11.0]String data, right truncation' 
ODBC_READER: TPT17101: Fatal error received from ODBC driver:
STATE=01004, CODE=0,

所用TPT脚本模板 #

USING CHAR SET ASCII
DEFINE JOB JobName
DESCRIPTION 'Load data from SQL(tablename) to Teradata(tablename)'


(
  DEFINE OPERATOR Database_Commands()
  TYPE DDL
  ATTRIBUTES
  (
    VARCHAR TdpId = @Teradata_Connection,
    VARCHAR UserName = @Teradata_UserId,
    VARCHAR UserPassword = @Teradata_Password,
    VARCHAR Array ErrorList = ['2580'],
    VARCHAR PrivateLogName = 'ddllog.txt',
    VARCHAR Tracelevel='None',
    VARCHAR LogSQL = ''
 );
  
  /*Examples*/
  DEFINE SCHEMA SourceTableSchema
  (
     columnname      INTEGER,
     columnname2     VARCHAR(50),
     columnname3     VARCHAR(85),
     columnname4     VARCHAR(15),
     columnname4     BYTEINT    
  );
  
  
  DEFINE SCHEMA TargetTableSchema
  (
     columnname      INTEGER,
     columnname2     VARCHAR(50),
     columnname3     VARCHAR(85),
     columnname4     VARCHAR(15),
     columnname4     BYTEINT     
  );
  

  DEFINE OPERATOR ODBC_READER
  TYPE ODBC
  SCHEMA SourceTableSchema
  ATTRIBUTES
  (
      VARCHAR PrivateLogName = 'odbc_log.txt',  
      VARCHAR dsnName = 'SQLDatabaseName',                              
      VARCHAR UserName = @ODBC_UserId,        
      VARCHAR UserPassword = @ODBC_Password,
      VARCHAR TraceLevel = 'None',
      VARCHAR SelectStmt = @SQL 
  );
  
   
  DEFINE OPERATOR TD_LOADER()
  TYPE UPDATE
  SCHEMA TargetTableSchema
  ATTRIBUTES
  (
    VARCHAR TargetTable = @TargetTable,
    VARCHAR LogTable    = @ErrDatabase||'.'||@TargetTable||'_LT', 
    VARCHAR WorkTable   = @WorkDatabase||'.'||@TargetTable||'_WT',
    VARCHAR ErrorTable1 = @ErrDatabase||'.'||@TargetTable||'_ET',
    VARCHAR ErrorTable2 = @ErrDatabase||'.'||@TargetTable||'_UV',
    VARCHAR DateForm = 'IntegerDate',
    VARCHAR TdpId = @Teradata_Connection,
    VARCHAR UserName = @Teradata_UserId,
    VARCHAR UserPassword = @Teradata_Password,
    VARCHAR WorkingDatabase = @TargetDatabase,
    VARCHAR PrivateLogName = 'td_loader_log.txt'
  );

STEP RELEASE
  (
     APPLY
           ' release mload '|| @TargetDatabase ||'.'||@TargetTable ||'  ;'
     TO OPERATOR (Database_Commands());
   );


STEP Initialize
  (
     APPLY
           ' delete from '|| @TargetDatabase ||'.'||@TargetTable ||' '||@DeleteScope||' ;'
     TO OPERATOR (Database_Commands());
   );


STEP load_the_data
  (
    APPLY 
         ('INSERT INTO '|| @TargetDatabase ||'.'||@TargetTable||'
                            (   
                            
                             ...columnnames...

                            );'
          )
    TO OPERATOR (TD_LOADER)
       SELECT                       
                                   
              ...columnnames...
    
         FROM OPERATOR (ODBC_READER); 
  );
);

已确认源表与目标表结构一致（目标表基于源表定义创建），但错误依然发生。

解决方案 #

核心原因 #

String data, right truncation错误本质是SQL Server中某行的字符串数据长度超过了TPT源Schema定义的长度限制，常见触发场景：

• TPT脚本SourceTableSchema中定义的字符串长度小于SQL Server源表实际字段长度
• SQL Server使用NVARCHAR类型，TPT用VARCHAR读取时，Unicode字符占用2字节导致字节数超限
• 源表中新增了超长数据，超出了之前Schema定义的长度范围

具体修复步骤 #

1. 核对Schema与源表字段定义

   • 逐一对比SQL Server源表字段的类型、长度，确保SourceTableSchema中的VARCHAR/NVARCHAR长度与源表完全匹配
   • 若源表是NVARCHAR(n)，TPT Schema可对应定义为NVARCHAR(n)，或设置为VARCHAR(2n)（适配Unicode字节占用）

2. 定位源表中的超长数据
   执行SQL查询找出第一行触发截断的字段：

   -- 替换为实际表名和字段名，逐个排查字符串字段
   SELECT columnname2, LEN(columnname2) AS char_length, DATALENGTH(columnname2) AS byte_length
   FROM SQLServerTableName
   WHERE DATALENGTH(columnname2) > 50 -- 对应Schema中定义的字节长度
   ORDER BY byte_length DESC
   

   注意：LEN()返回字符数，DATALENGTH()返回字节数，NVARCHAR类型的字节数是字符数的2倍。

3. 调整TPT ODBC Reader配置

   • 临时绕过错误（谨慎使用，会截断数据）：在ODBC_READER的ATTRIBUTES中添加VARCHAR AllowTruncation = 'Yes'
   • 定位具体字段：将TraceLevel改为'Verbose'，查看odbc_log.txt中的详细日志，确认是哪个字段触发截断

4. 检查字符集设置
   若源表包含中文或其他Unicode字符，将脚本开头的USING CHAR SET ASCII改为USING CHAR SET UTF8，避免字符转换导致的长度膨胀。

内容的提问来源于stack exchange，提问作者DirtyDataDoneDirtCheap