Print
All Forums Tools
TDHS9 5 posts Joined 04/12
06 Dec 2012
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_nam e,display_on_mailmanager,query_id,org_id,add_user,add_ datetime,upd_user,upd_datetime,sql_source,list_count,d istinct_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_coun t) 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,c ategory_code,source_code,cost,purchased_from,file_name ,display_on_mailmanager,query_id,org_id,add_user,add_d atetime,upd_user,upd_datetime,sql_source,list_count,di stinct_acct_count,mm_sub_type,mm_last_run_datetime,mm_ include_in_list,dynamic_list,source_list_id,mm_admin_l ist_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_nam e,display_on_mailmanager,query_id,org_id,add_user,add_ datetime,upd_user,upd_datetime,sql_source,list_count,d istinct_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_coun t) 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

Tags:
feinholz 1234 posts Joined 05/08
20 Dec 2012

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

You must sign in to leave a comment.