TPT Error : TPT17134: Terminating due to 1 or more incompatible columns and TruncateData set to FALSE

Tools
Enthusiast

TPT Error : TPT17134: Terminating due to 1 or more incompatible columns and TruncateData set to FALSE

Hi

I’m using TPT Wizard and using TPT Load operator(Fastload) to generate scripts. It’s working fine for most of the tables but it’s throwing above error for couple of tables. At the same time If I use OLD Load and generate Multiload script, It works for all the tables.

TPT Load Script

USING CHARACTER SET ASCII

DEFINE JOB TM_T_List_17t

(

                DEFINE OPERATOR W_1_o_TM_T_List_17t

                TYPE LOAD

                SCHEMA *

                ATTRIBUTES

                (

                                VARCHAR UserName,

                                VARCHAR UserPassword,

                                VARCHAR LogTable,

                                VARCHAR TargetTable,

                                INTEGER BufferSize,

                                INTEGER ErrorLimit,

                                INTEGER MaxSessions,

                                INTEGER MinSessions,

                                INTEGER TenacityHours,

                                INTEGER TenacitySleep,

                                VARCHAR AccountID,

                                VARCHAR DateForm,

                                VARCHAR ErrorTable1,

                                VARCHAR ErrorTable2,

                                VARCHAR NotifyExit,

                                VARCHAR NotifyExitIsDLL,

                                VARCHAR NotifyLevel,

                                VARCHAR NotifyMethod,

                                VARCHAR NotifyString,

                                VARCHAR PauseAcq,

                                VARCHAR PrivateLogName,

                                VARCHAR TdpId,

                                VARCHAR TraceLevel,

                                VARCHAR WorkingDatabase

                );

                DEFINE SCHEMA W_0_s_TM_T_List_17t

                (

                                list_id INTEGER,

                                name_0 VARCHAR(30),

                                description_1 VARCHAR(255),

                                list_type VARCHAR(1),

                                frozen VARCHAR(1),

                                purge_date CHARACTER(23),

                                category_code VARCHAR(3),

                                source_code VARCHAR(3),

                                cost DECIMAL(12, 2),

                                purchased_from VARCHAR(30),

                                file_name VARCHAR(255),

                                display_on_mailmanager VARCHAR(1),

                                query_id INTEGER,

                                org_id INTEGER,

                                add_user VARCHAR(8),

                                add_datetime CHARACTER(23),

                                upd_user VARCHAR(8),

                                upd_datetime CHARACTER(23),

                                sql_source VARCHAR(2000),

                                list_count INTEGER,

                                distinct_acct_count INTEGER,

                                mm_sub_type VARCHAR(1),

                                mm_last_run_datetime CHARACTER(23),

                                mm_include_in_list VARCHAR(1),

                                dynamic_list VARCHAR(1),

                                source_list_id INTEGER,

                                mm_admin_list_type VARCHAR(1),

                                mm_primary_cust_name_ind VARCHAR(1),

                                distinct_email_count INTEGER

                );

                DEFINE OPERATOR W_0_o_TM_T_List_17t

                TYPE ODBC

                SCHEMA W_0_s_TM_T_List_17t

                ATTRIBUTES

                (

                                VARCHAR UserName,

                                VARCHAR UserPassword,

                                VARCHAR SelectStmt,

                                VARCHAR PrivateLogName,

                                VARCHAR DSNName,

                                VARCHAR ConnectString,

                                VARCHAR TruncateData

                );

                APPLY

                                (

                                                'INSERT INTO PRD_TM_STG_TB.T_List_17t (list_id,name,description,list_type,frozen,purge_date,category_code,source_code,cost,purchased_from,file_name,display_on_mailmanager,query_id,org_id,add_user,add_datetime,upd_user,upd_datetime,sql_source,list_count,distinct_acct_count,mm_sub_type,mm_last_run_datetime,mm_include_in_list,dynamic_list,source_list_id,mm_admin_list_type,mm_primary_cust_name_ind,distinct_email_count) VALUES (:list_id,:name_0,:description_1,:list_type,:frozen,:purge_date,:category_code,:source_code,:cost,:purchased_from,:file_name,:display_on_mailmanager,:query_id,:org_id,:add_user,:add_datetime,:upd_user,:upd_datetime,:sql_source,:list_count,:distinct_acct_count,:mm_sub_type,:mm_last_run_datetime,:mm_include_in_list,:dynamic_list,:source_list_id,:mm_admin_list_type,:mm_primary_cust_name_ind,:distinct_email_count);'

                                )

                TO OPERATOR

                (

                                W_1_o_TM_T_List_17t[1]

                                ATTRIBUTES

                                (

                                                UserName = @TargetUserName,

                                                UserPassword = @TargetPassword,

                                                LogTable = 'PRD_TM_STG_TB.T_List_17t_log',

                                                TargetTable = 'PRD_TM_STG_TB.T_List_17t',

                                                TdpId = @TargetTdpId

                                )

                )

                SELECT * FROM OPERATOR

                (

                                W_0_o_TM_T_List_17t[1]

                                ATTRIBUTES

                                (

                                                UserName = @SourceUserName,

                                                UserPassword = @SourcePassword,

                                                SelectStmt = 'SELECT list_id,name,description,list_type,frozen,purge_date,category_code,source_code,cost,purchased_from,file_name,display_on_mailmanager,query_id,org_id,add_user,add_datetime,upd_user,upd_datetime,sql_source,list_count,distinct_acct_count,mm_sub_type,mm_last_run_datetime,mm_include_in_list,dynamic_list,source_list_id,mm_admin_list_type,mm_primary_cust_name_ind,distinct_email_count FROM TDWORK.dbo.T_List_17;',

                                                DSNName = @SourceDSNName1

                                )

                );

);

When I execute this script It gives Following Error. I've also attached Log file below.

TPT17134: Terminating due to 1 or more incompatible columns and TruncateData set to FALSE

Detail log file

It shows one Warning: possible size violation for column: 'sql_source' -----as this column data type is 'text' in source table and it's assaigned  Varchar(2000) as per the target table.

- This type of warning is present in other successful jobs log files  as well.

- All the other Warnings(TMSM failed to initialize, Warning: Message received from ODBC driver: ... etc) are present in all the successful jobs log files as well, so please Ignore it.

TPT_INFRA: TPT04101: Warning: TMSM failed to initialize

Found CheckPoint file: C:\Program Files (x86)\Teradata\client\13.10\Teradata Parallel Transporter/checkpoint\TM_T_List_17tLVCP

This is a restart job; it restarts at step MAIN_STEP.

Teradata Parallel Transporter Coordinator Version 13.10.00.09

Teradata Parallel Transporter Executor Version 13.10.00.09

Teradata Parallel Transporter Executor Version 13.10.00.09

Teradata Parallel Transporter Load Operator Version 13.10.00.04

W_1_o_TM_T_List_17t: private log not specified

     ===================================================================

     =                                                                 =

     =                  TERADATA PARALLEL TRANSPORTER                  =

     =                                                                 =

     =              LOAD OPERATOR     VERSION 13.10.00.04              =

     =                                                                 =

     =          OPERATOR SUPPORT LIBRARY VERSION 13.10.00.09           =

     =                                                                 =

     = COPYRIGHT 2001-2011, TERADATA CORPORATION. ALL RIGHTS RESERVED. =

     =                                                                 =

     ===================================================================

**** 10:00:35 Processing starting at: Thu Dec 06 10:00:35 2012

     ===================================================================

     =                                                                 =

     =                      Module Identification                      =

     =                                                                 =

     ===================================================================

     Load Operator for Windows on AAA03

     LoadMain   : 13.10.00.09

     LoadCLI    : 13.10.00.09

     LoadUtil   : 13.10.00.02

     PcomCLI    : 13.10.00.25

     PcomMBCS   : 13.00.00.02

     PcomMsgs   : 13.01.00.07

     PcomNtfy   : 13.00.00.03

     PcomPx     : 13.10.00.19

     PcomUtil   : 13.10.00.09

     PXICU      : 13.10.00.09

Teradata Parallel Transporter ODBC Operator Version 13.10.00.09

W_0_o_TM_T_List_17t: private log not specified

     ===================================================================

     =                                                                 =

     =                  TERADATA PARALLEL TRANSPORTER                  =

     =                                                                 =

     =              ODBC OPERATOR     VERSION 13.10.00.09              =

     =                                                                 =

     =          OPERATOR SUPPORT LIBRARY VERSION 13.10.00.09           =

     =                                                                 =

     = COPYRIGHT 2001-2011, TERADATA CORPORATION. ALL RIGHTS RESERVED. =

     =                                                                 =

     ===================================================================

**** 10:00:35 Processing starting at: Thu Dec 06 10:00:35 2012

     ===================================================================

     =                                                                 =

     =                      Module Identification                      =

     =                                                                 =

     ===================================================================

     ODBC Operator for Windows on AAA03

     ODBCMain   : 13.01.00.01

     ODBCSQL    : 13.10.00.04

     ODBCUtil   : 13.10.00.04

     PcomCLI    : 13.10.00.25

     PcomMBCS   : 13.00.00.02

     PcomMsgs   : 13.01.00.07

     PcomNtfy   : 13.00.00.03

     PcomPx     : 13.10.00.19

     PcomUtil   : 13.10.00.09

     PXICU      : 13.10.00.09

     TDICU      : 13.10.00.02

     TDICU      : 13.10.00.02

     ===================================================================

     =                                                                 =

     =                      Attribute Definitions                      =

     =                                                                 =

     ===================================================================

**** 10:00:35 Options in effect for this job:

              OperatorType:  Producer

              Instances:     1

              Character set: 'ASCII'

              Checkpoint:    300 second(s)

     ===================================================================

     =                                                                 =

     =                     SQL Session Connection                      =

     =                                                                 =

     ===================================================================

**** 10:00:35 Connecting to RDBMS:    'AAA03'

**** 10:00:35 Connecting with UserId: 'XXXXX'

W_0_o_TM_T_List_17t: connecting sessions

W_0_o_TM_T_List_17t: TPT17176: Info: Message received from ODBC driver:

STATE=01000, CODE=5701,

MSG='[Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'master'.'

**** 10:00:36 TPT17176: Info: Message received from ODBC driver:

STATE=01000, CODE=5701,

MSG='[Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'master'.'

W_0_o_TM_T_List_17t: TPT17176: Info: Message received from ODBC driver:

STATE=01000, CODE=5703,

MSG='[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english.'

**** 10:00:36 TPT17176: Info: Message received from ODBC driver:

STATE=01000, CODE=5703,

MSG='[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english.'

W_0_o_TM_T_List_17t: TPT17175: Warning: Message received from ODBC driver:

STATE=S1092, CODE=0,

MSG='[Microsoft][ODBC SQL Server Driver]Invalid attribute/option identifier'

**** 10:00:36 TPT17175: Warning: Message received from ODBC driver:

STATE=S1092, CODE=0,

MSG='[Microsoft][ODBC SQL Server Driver]Invalid attribute/option identifier'

     ===================================================================

     =                                                                 =

     =                     ODBC Driver Information                     =

     =                                                                 =

     ===================================================================

     ODBC version        = '03.52.0000'

     DBMS name           = 'Microsoft SQL Server'

     DBMS version        = '10.50.1600'

     Driver name         = 'SQLSRV32.DLL'

     Driver version      = '03.86.3959'

     Driver ODBC version = '03.52'

     ===================================================================

     =                                                                 =

     =               Source Table Column/Field Defintion               =

     =                                                                 =

     ===================================================================

     CLIv2      : 13.10.00.08  

     ===================================================================

     =                                                                 =

     =                      Attribute Definitions                      =

     =                                                                 =

     ===================================================================

**** 10:00:38 Options in effect for this job:

              OperatorType:  Consumer

              Instances:     1

              Character set: 'ASCII'

              Checkpoint:    300 second(s)

              Notify:        Not enabled

              Buffer size:   Maximum allowable

              Error limit:   No limit in effect

              Tenacity:      4 hour limit to successfully connect

              Sleep:         6 minute(s) between connect retries

              Pause Acq:     Not in effect

     ===================================================================

     =                                                                 =

     =                     Column/Field Definition                     =

     =                                                                 =

     ===================================================================

     Column Name                    Offset Length Type     

     ============================== ====== ====== ========================

     list_id                             0      4 INTEGER

     name_0                              4     30 VARCHAR

     description_1                      36    255 VARCHAR

     list_type                         293      1 VARCHAR

     frozen                            296      1 VARCHAR

     purge_date                        299     23 CHAR

     category_code                     322      3 VARCHAR

     source_code                       327      3 VARCHAR

     cost                              332      8 DECIMAL(12,2)

     purchased_from                    340     30 VARCHAR

     file_name                         372    255 VARCHAR

     display_on_mailmanager            629      1 VARCHAR

     query_id                          632      4 INTEGER

     org_id                            636      4 INTEGER

     add_user                          640      8 VARCHAR

     add_datetime                      650     23 CHAR

     upd_user                          673      8 VARCHAR

     upd_datetime                      683     23 CHAR

     sql_source                        706   2000 VARCHAR

     list_count                       2708      4 INTEGER

     distinct_acct_count              2712      4 INTEGER

     mm_sub_type                      2716      1 VARCHAR

     mm_last_run_datetime             2719     23 CHAR

     mm_include_in_list               2742      1 VARCHAR

     dynamic_list                     2745      1 VARCHAR

     source_list_id                   2748      4 INTEGER

     mm_admin_list_type               2752      1 VARCHAR

     mm_primary_cust_name_ind         2755      1 VARCHAR

     distinct_email_count             2758      4 INTEGER

     ============================== ====== ====== ========================

     INDICATOR BYTES NEEDED: 4

     EXPECTED RECORD LENGTH: 2766

     ===================================================================

     =                                                                 =

     =                   Control Session Connection                    =

     =                                                                 =

     ===================================================================

W_1_o_TM_T_List_17t: connecting sessions

**** 10:00:38 Connecting to RDBMS:    'TDXXX'

**** 10:00:38 Connecting with UserId: 'XXXXX'

     ===================================================================

     =                                                                 =

     =                  Teradata Database Information                  =

     =                                                                 =

     ===================================================================

**** 10:00:40 Teradata Database Version:      '13.10.04.08                     '

**** 10:00:40 Teradata Database Release:      '13.10.04.08                   '

**** 10:00:40 Maximum request size supported: 1MB

**** 10:00:40 Session character set:          'ASCII'

**** 10:00:40 Total AMPs available:           144

**** 10:00:40 Data Encryption:                supported

**** 10:00:43 Restart log table 'PRD_STG_TB.T_List_17t_log' has been created

     ===================================================================

     =                                                                 =

     =                   Special Session Connection                    =

     =                                                                 =

     ===================================================================

     Column Name                    Offset Length Type     

     ============================== ====== ====== ========================

     list_id                             0      4 int

     name                                4     30 varchar

     description                        34    255 varchar

     list_type                         289      1 varchar

     frozen                            290      1 varchar

     purge_date                        291     23 datetime

     category_code                     314      3 varchar

     source_code                       317      3 varchar

     cost                              320     14 decimal(12,2)

     purchased_from                    334     30 varchar

     file_name                         364    255 varchar

     display_on_mailmanager            619      1 varchar

     query_id                          620      4 int

     org_id                            624      4 int

     add_user                          628      8 varchar

     add_datetime                      636     23 datetime

     upd_user                          659      8 varchar

     upd_datetime                      667     23 datetime

     sql_source                        690 2147483647 text

     list_count                     -2147482959      4 int

     distinct_acct_count            -2147482955      4 int

     mm_sub_type                    -2147482951      1 varchar

     mm_last_run_datetime           -2147482950     23 datetime

     mm_include_in_list             -2147482927      1 varchar

     dynamic_list                   -2147482926      1 varchar

     source_list_id                               -2147482925      4 int

     mm_admin_list_type                    -2147482921      1 varchar

     mm_primary_cust_name_ind       -2147482920      1 varchar

     distinct_email_count                    -2147482919      4 int

     ============================== ====== ====== ========================

     EXPECTED RECORD LENGTH: -2147482915

     ===================================================================

     =                                                                 =

     =                     Column/Field Definition                     =

     =                                                                 =

     ===================================================================

     Column Name                    Offset Length Type     

     ============================== ====== ====== ========================

     list_id                             0      4 INTEGER

     name_0                              4     30 VARCHAR

     description_1                      36    255 VARCHAR

     list_type                         293      1 VARCHAR

     frozen                            296      1 VARCHAR

     purge_date                        299     23 CHAR

     category_code                     322      3 VARCHAR

     source_code                       327      3 VARCHAR

     cost                              332      8 DECIMAL(12,2)

     purchased_from                    340     30 VARCHAR

     file_name                         372    255 VARCHAR

     display_on_mailmanager            629      1 VARCHAR

     query_id                          632      4 INTEGER

     org_id                            636      4 INTEGER

     add_user                          640      8 VARCHAR

     add_datetime                      650     23 CHAR

     upd_user                          673      8 VARCHAR

     upd_datetime                      683     23 CHAR

     sql_source                        706   2000 VARCHAR

     list_count                       2708      4 INTEGER

     distinct_acct_count              2712      4 INTEGER

     mm_sub_type                      2716      1 VARCHAR

     mm_last_run_datetime             2719     23 CHAR

     mm_include_in_list               2742      1 VARCHAR

     dynamic_list                     2745      1 VARCHAR

     source_list_id                   2748      4 INTEGER

     mm_admin_list_type               2752      1 VARCHAR

     mm_primary_cust_name_ind         2755      1 VARCHAR

     distinct_email_count             2758      4 INTEGER

     ============================== ====== ====== ========================

     INDICATOR BYTES NEEDED: 4

     EXPECTED RECORD LENGTH: 2766

     Warning: possible size violation for column: 'sql_source'

W_0_o_TM_T_List_17t: TPT17134: Terminating due to 1 or more incompatible columns and

TruncateData set to FALSE

**** 10:00:47 TPT17134: Terminating due to 1 or more incompatible columns and

TruncateData set to FALSE

TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0

     ===================================================================

     =                                                                 =

     =                        Logoff/Disconnect                        =

     =                                                                 =

     ===================================================================

W_0_o_TM_T_List_17t: disconnecting sessions

**** 10:00:47 Logging off all sessions

              Instance      Cpu Time    

              ========  ================

                   1        0.47 Seconds

**** 10:00:47 Total processor time used = '0.46875 Second(s)'

.        Start : Thu Dec 06 10:00:35 2012

.        End   : Thu Dec 06 10:00:47 2012

.        Highest return code encountered = '12'.

W_0_o_TM_T_List_17t: Total processor time used = '0.46875 Second(s)'

W_0_o_TM_T_List_17t: Start : Thu Dec 06 10:00:35 2012

W_0_o_TM_T_List_17t: End   : Thu Dec 06 10:00:47 2012

**** 10:00:47 This job terminated

**** 10:01:38 Maximum number of special sessions requested: All

**** 10:01:38 Minimum number of special sessions required:  1

              Instance Assigned Connected Result               

              ======== ======== ========= ======================

                  1       144      144    Successful

              ======== ======== ========= ======================

                Total     144      144    Successful

     ===================================================================

     =                                                                 =

     =                 Target/Error Table Information                  =

     =                                                                 =

     ===================================================================

W_1_o_TM_T_List_17t: preparing target table

**** 10:01:38 This job will use the following tables:

              Target Table:  'PRD_STG_TB.T_List_17t'

              Error Table 1: 'PRD_STG_TB.T_List_17t_ET'

              Error Table 2: 'PRD_STG_TB.T_List_17t_UV'

     ===================================================================

     =                                                                 =

     =                        Acquisition Phase                        =

     =                                                                 =

     ===================================================================

W_1_o_TM_T_List_17t: entering Acquisition Phase

**** 10:01:43 DML statement for DML Group: 1

     INSERT INTO PRD_STG_TB.T_List_17t (list_id,name,description,list_type,frozen,purge_date,category_code,source_code,cost,purchased_from,file_name,display_on_mailmanager,query_id,org_id,add_user,add_datetime,upd_user,upd_datetime,sql_source,list_count,distinct_acct_count,mm_sub_type,mm_last_run_datetime,mm_include_in_list,dynamic_list,source_list_id,mm_admin_list_type,mm_primary_cust_name_ind,distinct_email_count) VALUES (:list_id,:name_0,:description_1,:list_type,:frozen,:purge_date,:category_code,:source_code,:cost,:purchased_from,:file_name,:display_on_mailmanager,:query_id,:org_id,:add_user,:add_datetime,:upd_user,:upd_datetime,:sql_source,:list_count,:distinct_acct_count,:mm_sub_type,:mm_last_run_datetime,:mm_include_in_list,:dynamic_list,:source_list_id,:mm_admin_list_type,:mm_primary_cust_name_ind,:distinct_email_count);

**** 10:01:43 Number of records per buffer for this job: 23

Job is running in Buffer Mode

TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0

     ===================================================================

     =                                                                 =

     =                        Logoff/Disconnect                        =

     =                                                                 =

     ===================================================================

W_1_o_TM_T_List_17t: disconnecting sessions

**** 10:01:43 Logging off all sessions

              Instance      Cpu Time    

              ========  ================

                   1        2.50 Seconds

**** 10:03:15 Total processor time used = '2.5 Second(s)'

.        Start : Thu Dec 06 10:00:35 2012

.        End   : Thu Dec 06 10:03:15 2012

.        Highest return code encountered = '0'.

W_1_o_TM_T_List_17t: Total processor time used = '2.5 Second(s)'

W_1_o_TM_T_List_17t: Start : Thu Dec 06 10:00:35 2012

W_1_o_TM_T_List_17t: End   : Thu Dec 06 10:03:15 2012

**** 10:03:15 This job terminated

Job step MAIN_STEP terminated (status 12)

Job TM_T_List_17t terminated (status 12)

Total available memory:          10000000

Largest allocable area:          10000000

Memory use high water mark:         46256

Free map size:                       1024

Free map use high water mark:          16

Free list use high water mark:          0

I've tried to search above error but couldn't find any information.

I'll appreciate the help from forum members to solve this problem.

HS

1 REPLY
Teradata Employee

Re: TPT Error : TPT17134: Terminating due to 1 or more incompatible columns and TruncateData set to FALSE

If you look at the output, you will see one schema defined with "sql_source" like this:

 sql_source                        706   2000 VARCHAR

and in the other location, like this:

sql_source                        690 2147483647 text

Clearly, something is wrong somewhere.

You will need to open an incident with the GSC (global support center) and have them help you research the issue.

I will also note that you are using the SQLServer ODBC driver, and not an ODBC driver that is certified with TPT. Since we only certify with the ODBC drivers provided by Progress DataDirect, you should download an evaluation copy of that driver and try out the test. If you still get the same issue, then contact the GSC. If the problem goes away, there must be something about the SQLServer driver that was erroneous.

The GSC might only assist if you are using supported 3rd party products.

-- SteveF