TPT load script fails with incompatible message

Tools & Utilities
Enthusiast

TPT load script fails with incompatible message

Hi,

Your advice with the following will be greatly appreciated.

I am using the TTU 14.10 set of tools with a Teradata DB 14.0.2.11

With the OLELoad tool, The Fastload utility works perfectly but for some reason the TPT utility fails with the following message:

"Teradata OleLoad has detected a problem in the request from the calling software "execute.exe". It is possible that the calling software is incompatible with the access module"

Is there a way around this problem ?... Would using TTU 13.x resolve this problem ?... If so, what is the download URL for TTU 13.x ?... (I looked everywhere for it)....

Thank you for your time.

Boris. (boris.cb@weareperitas.com)

14 REPLIES
Teradata Employee

Re: TPT load script fails with incompatible message

Never seen that message before.

The OLE-DB Access module has no knowledge of who is calling it (that I know of).

Please reply with the full TPT script.

Thanks!

-- SteveF
Teradata Employee

Re: TPT load script fails with incompatible message

Hi Boris,

Should not be problem while using TPT.

Can you please provide more details on the problem to get reproduced.

> Was the import job submitted using OleLoad tool or it was executed in batch mode?

> Please provide full TPT script.

> What is the source database and how it was connected.

> Was the .amj file saved before used in TPT script?

> Was the TPT script created separately/manually, or the scipt was created using OleLoad tool?

Thanks !!

-Sudhansu

Enthusiast

Re: TPT load script fails with incompatible message

Hi Sudhansu,

I desperately need your help with this. I am new to TPT so I am not sure what to do here. Your assistance is greatly appreciated.

Below are the result lines from running the TPT script created by OLELoad (Fastload runs with no problem for the same tables in this wizard).

Teradata Parallel Transporter Version 14.10.00.02

Job log: C:\Program Files\Teradata\client\14.10\Teradata Parallel Transporter/logs/OleLoad-yae620yg

Job id is OleLoad-yae620yg2t-29, running on DG3

Teradata Parallel Transporter SQL DDL Operator Version 14.10.00.02

DDLOperator: private log specified: ddl_log

DDLOperator: connecting sessions

DDLOperator: sending SQL requests

DDLOperator: disconnecting sessions

DDLOperator: Total processor time used = '0.109375 Second(s)'

DDLOperator: Start : Fri Jan 10 14:12:54 2014

DDLOperator: End   : Fri Jan 10 14:12:57 2014

Job step create_the_table completed successfully

Teradata Parallel Transporter Load Operator Version 14.10.00.02

MyConsumer: private log specified: consumer_log

Teradata Parallel Transporter DataConnOper: TPT19006 Version 14.10.00.02

DataConnOper Instance 1 directing private log report to 'producer_log-1'.

DataConnOper: TPT19003 NotifyMethod: 'None (default)'

DataConnOper: TPT19008 DataConnector Producer operator Instances: 1

MyConsumer: connecting sessions

MyConsumer: preparing target table

MyConsumer: entering Acquisition Phase

DataConnOper: TPT19003 ECI operator ID: DataConnOper-9768

DataConnOper: TPT19127 WARNING file 'Untitled' not processed.

DataConnOper: TPT19003 The system cannot find the file specified.

DataConnOper: TPT19003 TPT Exit code set to 12.

DataConnOper: TPT19221 Total files processed: 0.

MyConsumer: disconnecting sessions

MyConsumer: Total processor time used = '0.84375 Second(s)'

MyConsumer: Start : Fri Jan 10 14:13:01 2014

MyConsumer: End   : Fri Jan 10 14:13:42 2014

Job step load_the_data terminated (status 12)

Job OleLoad-yae620yg2t terminated (status 12)

Job start: Fri Jan 10 14:12:53 2014

Job end:   Fri Jan 10 14:13:42 2014

This is the script generated by the wizard:

SET MyTdpId='xxx.xx.x.x';

SET MyUserName='xxxxxx';

SET MyPassword='xxxxx';

SET MyDatabase='Dev_DBTEST';

USING CHAR SET UTF8

DEFINE JOB MyJob

(

  DEFINE SCHEMA MySchema

  (

    WorkflowId              INTEGER,

    "Name"                  VARCHAR(150),

    WorkflowTypeId          INTEGER,

    WorkflowTypeServiceName VARCHAR(3000),

    IsCurrent               BYTEINT

  );

  DEFINE OPERATOR DDLOperator()

  TYPE DDL

  ATTRIBUTES

  (

    VARCHAR PrivateLogName = 'ddl_log',

    VARCHAR TdpId = @MyTdpId,

    VARCHAR UserName = @MyUserName,

    VARCHAR UserPassword = @MyPassword,

    VARCHAR WorkingDatabase = @MyDatabase

  );

  DEFINE OPERATOR DataConnOper()

  TYPE DATACONNECTOR PRODUCER

  SCHEMA MySchema

  ATTRIBUTES

  (

    VARCHAR AccessModuleName = 'Oledb_Axsmod',

    VARCHAR AccessModuleInitStr = 'noprompt jobid=1',

    VARCHAR FileName = 'Untitled',

    VARCHAR Format = 'Formatted',

    VARCHAR EnableScan = 'No',

    VARCHAR IndicatorMode = 'Yes',

    VARCHAR PrivateLogName = 'producer_log'

  );

  DEFINE OPERATOR MyConsumer()

  TYPE LOAD

  SCHEMA MySchema

  ATTRIBUTES

  (

    VARCHAR DateForm = 'IntegerDate',

    VARCHAR ErrorTable1 = 'Workflow_errors1',

    VARCHAR ErrorTable2 = 'Workflow_errors2',

    VARCHAR LogTable = '"Dev_DBTEST"."Workflow_Log"',

    VARCHAR PrivateLogName = 'consumer_log',

    VARCHAR TargetTable = '"Workflow"',

    VARCHAR TdpId = @MyTdpId,

    VARCHAR UserName = @MyUserName,

    VARCHAR UserPassword = @MyPassword,

    VARCHAR WorkingDatabase = @MyDatabase

  );

  STEP create_the_table

  (

    APPLY

    ('CREATE MULTISET TABLE "Workflow" ( WorkflowId              INTEGER NOT NULL ,

                                         "Name"                  VARCHAR(50) CHARACTER SET UNICODE NOT NULL CASESPECIFIC  ,

                                         WorkflowTypeId          INTEGER NOT NULL ,

                                         WorkflowTypeServiceName VARCHAR(1000) CHARACTER SET UNICODE NOT NULL CASESPECIFIC  ,

                                         IsCurrent               BYTEINT NOT NULL );')

      TO OPERATOR (DDLOperator);

  );

  STEP load_the_data

  (

    APPLY

    ('INSERT INTO "Workflow" ( :WorkflowId, :"Name", :WorkflowTypeId, 

                               :WorkflowTypeServiceName, :IsCurrent );')

      TO OPERATOR (MyConsumer)

      SELECT * FROM OPERATOR (DataConnOper);

  );

);

The error dialog that pops-up a few times is:

Oledb_axsmod

Teradata OleLoad has detected a problem in the request from the calling software, “executor.exe”. It is possible that the calling software version is incompatible with this access module.

THANK YOU

Boris.

Teradata Employee

Re: TPT load script fails with incompatible message

Hi Boris,

Are you using 64bit of TPT with 32 bit OleLoad? Please use 32bit TPT along with 32bit OleLoad.

Additionally, Can you please generate TPT trace file by adding following lines in the OleLoad generated script?

VARCHAR TraceLevel            = 'ALL'

Enthusiast

Re: TPT load script fails with incompatible message

Hi,

Thank you for your assistance with this...

Using the Teradata Parallel Transporter wizard (instead of the OLELoad) , I have tried both MSSQL ODBC and DataDirect ODBC 32 bits. 64 bits gives me an imcompatible message right away... But I get the following Job output failure report with any of the 32 bit ODBC DNS connections:

I don't know what else to look for anymore ... 

Teradata Parallel Transporter Version 14.10.00.02

Job log: C:\Program Files (x86)\Teradata\client\14.10\Teradata Parallel Transporter/logs/JOBWorkflow4-8.out

Job id is JOBWorkflow4-8, running on WAREHOUSEBUILD1

Teradata Parallel Transporter Load Operator Version 14.10.00.02

W_1_o_Workflow: private log not specified

Teradata Parallel Transporter ODBC Operator Version 14.10.00.02

W_0_o_Workflow: private log not specified

W_0_o_Workflow: connecting sessions

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

STATE=01000, CODE=5701,

MSG='[Microsoft][SQL Server Native Client 10.0][SQL Server]Changed database context to 'DEV_DB'.' 

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

STATE=01000, CODE=5703,

MSG='[Microsoft][SQL Server Native Client 10.0][SQL Server]Changed language setting to us_english.' 

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

STATE=S1092, CODE=0,

MSG='[Microsoft][SQL Server Native Client 10.0]Invalid attribute/option identifier' 

W_1_o_Workflow: connecting sessions

W_1_o_Workflow: preparing target table

W_1_o_Workflow: entering Acquisition Phase

W_1_o_Workflow: TPT10508: RDBMS error 3857: Cannot use value (or macro parameter) to match 'Name_6'.

W_1_o_Workflow: disconnecting sessions

W_1_o_Workflow: Total processor time used = '2.03125 Second(s)'

W_1_o_Workflow: Start : Tue Jan 14 08:44:00 2014

W_1_o_Workflow: End   : Tue Jan 14 08:44:08 2014

W_0_o_Workflow: disconnecting sessions

W_0_o_Workflow: Total processor time used = '0.0625 Second(s)'

W_0_o_Workflow: Start : Tue Jan 14 08:44:00 2014

W_0_o_Workflow: End   : Tue Jan 14 08:44:08 2014

Job step MAIN_STEP terminated (status 12)

Job JOBWorkflow4 terminated (status 12)

Job start: Tue Jan 14 08:43:59 2014

Job end:   Tue Jan 14 08:44:08 2014


The Job script:

/* 1 */  

/* 2 */  

/* 3 */  DEFINE JOB Workflow

/* 4 */  (

/* 5 */   DEFINE OPERATOR W_1_o_Workflow

/* 6 */   TYPE LOAD

/* 7 */   SCHEMA *

/* 8 */   ATTRIBUTES

/* 9 */   (

/* 10 */   VARCHAR UserName, 

/* 11 */   VARCHAR UserPassword, 

/* 12 */   VARCHAR LogTable, 

/* 13 */   VARCHAR TargetTable, 

/* 14 */   INTEGER BufferSize, 

/* 15 */   INTEGER ErrorLimit, 

/* 16 */   INTEGER MaxSessions, 

/* 17 */   INTEGER MinSessions, 

/* 18 */   INTEGER TenacityHours, 

/* 19 */   INTEGER TenacitySleep, 

/* 20 */   VARCHAR AccountID, 

/* 21 */   VARCHAR DateForm, 

/* 22 */   VARCHAR ErrorTable1, 

/* 23 */   VARCHAR ErrorTable2, 

/* 24 */   VARCHAR NotifyExit, 

/* 25 */   VARCHAR NotifyExitIsDLL, 

/* 26 */   VARCHAR NotifyLevel, 

/* 27 */   VARCHAR NotifyMethod, 

/* 28 */   VARCHAR NotifyString, 

/* 29 */   VARCHAR PauseAcq, 

/* 30 */   VARCHAR PrivateLogName, 

/* 31 */   VARCHAR TdpId, 

/* 32 */   VARCHAR TraceLevel, 

/* 33 */   VARCHAR WorkingDatabase

/* 34 */   );

/* 35 */  

/* 36 */   DEFINE SCHEMA W_0_s_Workflow

/* 37 */   (

/* 38 */   WorkflowId INTEGER,

/* 39 */   Name_3 VARCHAR(50),

/* 40 */   WorkflowTypeId INTEGER,

/* 41 */   WorkflowTypeServiceName VARCHAR(1000),

/* 42 */   IsCurrent BYTEINT

/* 43 */   );

/* 44 */  

/* 45 */   DEFINE OPERATOR W_0_o_Workflow

/* 46 */   TYPE ODBC

/* 47 */   SCHEMA W_0_s_Workflow

/* 48 */   ATTRIBUTES

/* 49 */   (

/* 50 */   VARCHAR UserName, 

/* 51 */   VARCHAR UserPassword, 

/* 52 */   VARCHAR SelectStmt, 

/* 53 */   VARCHAR PrivateLogName, 

/* 54 */   VARCHAR DSNName, 

/* 55 */   VARCHAR ConnectString, 

/* 56 */   VARCHAR TruncateData

/* 57 */   );

/* 58 */  

/* 59 */   APPLY

/* 60 */   (

/* 61 */   'INSERT INTO Dev_DB_LAND.Workflow (WorkflowId,Name,WorkflowTypeId,WorkflowTypeServiceName,IsCurrent) VALUES (:WorkflowId,:Name_4,:WorkflowTypeId,:WorkflowTypeServiceName,:IsCurrent);'

/* 62 */   )

/* 63 */   TO OPERATOR

/* 64 */   (

/* 65 */   W_1_o_Workflow[1]

/* 66 */  

/* 67 */   ATTRIBUTES

/* 68 */   (

/* 69 */   UserName = @TeradataDestinationUser, 

/* 70 */   UserPassword = @TeradataDestinationPassword, 

/* 71 */   LogTable = 'Dev_DB_LAND.Workflow_log', 

/* 72 */   TargetTable = 'Dev_DB_LAND.Workflow', 

/* 73 */   TdpId = '172.xx.xx.x'

/* 74 */   )

/* 75 */   )

/* 76 */   SELECT * FROM OPERATOR

/* 77 */   (

/* 78 */   W_0_o_Workflow[1]

/* 79 */  

/* 80 */   ATTRIBUTES

/* 81 */   (

/* 82 */   UserName = @OdbcSourceUser, 

/* 83 */   UserPassword = @OdbcSourcePassword, 

/* 84 */   SelectStmt = 'SELECT WorkflowId,Name,WorkflowTypeId,WorkflowTypeServiceName,IsCurrent FROM DEV_DB.App.Workflow;', 

/* 85 */   DSNName = 'SQL Server MS Client'

/* 86 */   )

/* 87 */   );

/* 88 */  );
Teradata Employee

Re: TPT load script fails with incompatible message

The error you are receiving is this:

TPT10508: RDBMS error 3857: Cannot use value (or macro parameter) to match 'Name_6'.

 

(the other messages are warnings because you are using a non-DataDirect driver).

Looks like your INSERT statement is not valid for the table you are loading.

What is the DDL for the target table?

-- SteveF
Enthusiast

Re: TPT load script fails with incompatible message

Hi,

Thank you for your feedback. I have also tried to exclude the "Name" column as it seems like it is a reserved word for Teradata ?... but it still gives me the following error:

Teradata Parallel Transporter Version 14.10.00.02

Job log: C:\Program Files (x86)\Teradata\client\14.10\Teradata Parallel Transporter/logs/JOBWorkflow7-12.out

Job id is JOBWorkflow7-12, running on WAREHOUSEBUILD1

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

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

Teradata Parallel Transporter ODBC Operator Version 14.10.00.02

Teradata Parallel Transporter Load Operator Version 14.10.00.02

W_0_o_Workflow: private log not specified

W_1_o_Workflow: private log not specified

W_0_o_Workflow: connecting sessions

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

STATE=01000, CODE=5701,

MSG='[Microsoft][SQL Server Native Client 10.0][SQL Server]Changed database context to 'DEV_DB'.' 

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

STATE=01000, CODE=5703,

MSG='[Microsoft][SQL Server Native Client 10.0][SQL Server]Changed language setting to us_english.' 

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

STATE=S1092, CODE=0,

MSG='[Microsoft][SQL Server Native Client 10.0]Invalid attribute/option identifier' 

W_1_o_Workflow: connecting sessions

W_1_o_Workflow: preparing target table

W_1_o_Workflow: entering Acquisition Phase

W_0_o_Workflow: sending SELECT request

W_0_o_Workflow: TPT15111: Error 19 allocating memory for data buffer

W_0_o_Workflow: disconnecting sessions

W_1_o_Workflow: disconnecting sessions

W_0_o_Workflow: Total processor time used = '0.03125 Second(s)'

W_0_o_Workflow: Start : Tue Jan 14 09:17:10 2014

W_0_o_Workflow: End   : Tue Jan 14 09:17:17 2014

W_1_o_Workflow: Total processor time used = '1.89063 Second(s)'

W_1_o_Workflow: Start : Tue Jan 14 09:17:10 2014

W_1_o_Workflow: End   : Tue Jan 14 09:17:19 2014

Job step MAIN_STEP terminated (status 12)

Job JOBWorkflow7 terminated (status 12)

Job start: Tue Jan 14 09:17:07 2014

Job end:   Tue Jan 14 09:17:19 2014

Below is the DDL for the table in Teradata:

CREATE MULTISET TABLE Dev_DB_LAND.Workflow ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      WorkflowId INTEGER NOT NULL,

      Name VARCHAR(50) CHARACTER SET UNICODE CASESPECIFIC NOT NULL,

      WorkflowTypeId INTEGER NOT NULL,

      WorkflowTypeServiceName VARCHAR(1000) CHARACTER SET UNICODE CASESPECIFIC NOT NULL,

      IsCurrent BYTEINT NOT NULL)

PRIMARY INDEX ( WorkflowId );


Below is the script: I cannot see what could be wrong with this....

/* 1 */  

/* 2 */  

/* 3 */  DEFINE JOB Workflow

/* 4 */  (

/* 5 */   DEFINE OPERATOR W_1_o_Workflow

/* 6 */   TYPE LOAD

/* 7 */   SCHEMA *

/* 8 */   ATTRIBUTES

/* 9 */   (

/* 10 */   VARCHAR UserName, 

/* 11 */   VARCHAR UserPassword, 

/* 12 */   VARCHAR LogTable, 

/* 13 */   VARCHAR TargetTable, 

/* 14 */   INTEGER BufferSize, 

/* 15 */   INTEGER ErrorLimit, 

/* 16 */   INTEGER MaxSessions, 

/* 17 */   INTEGER MinSessions, 

/* 18 */   INTEGER TenacityHours, 

/* 19 */   INTEGER TenacitySleep, 

/* 20 */   VARCHAR AccountID, 

/* 21 */   VARCHAR DateForm, 

/* 22 */   VARCHAR ErrorTable1, 

/* 23 */   VARCHAR ErrorTable2, 

/* 24 */   VARCHAR NotifyExit, 

/* 25 */   VARCHAR NotifyExitIsDLL, 

/* 26 */   VARCHAR NotifyLevel, 

/* 27 */   VARCHAR NotifyMethod, 

/* 28 */   VARCHAR NotifyString, 

/* 29 */   VARCHAR PauseAcq, 

/* 30 */   VARCHAR PrivateLogName, 

/* 31 */   VARCHAR TdpId, 

/* 32 */   VARCHAR TraceLevel, 

/* 33 */   VARCHAR WorkingDatabase

/* 34 */   );

/* 35 */  

/* 36 */   DEFINE SCHEMA W_0_s_Workflow

/* 37 */   (

/* 38 */   WorkflowId INTEGER,

/* 39 */   WorkflowTypeId INTEGER,

/* 40 */   WorkflowTypeServiceName VARCHAR(1000),

/* 41 */   IsCurrent BYTEINT

/* 42 */   );

/* 43 */  

/* 44 */   DEFINE OPERATOR W_0_o_Workflow

/* 45 */   TYPE ODBC

/* 46 */   SCHEMA W_0_s_Workflow

/* 47 */   ATTRIBUTES

/* 48 */   (

/* 49 */   VARCHAR UserName, 

/* 50 */   VARCHAR UserPassword, 

/* 51 */   VARCHAR SelectStmt, 

/* 52 */   VARCHAR PrivateLogName, 

/* 53 */   VARCHAR DSNName, 

/* 54 */   VARCHAR ConnectString, 

/* 55 */   VARCHAR TruncateData

/* 56 */   );

/* 57 */  

/* 58 */   APPLY

/* 59 */   (

/* 60 */   'INSERT INTO Dev_DB_LAND.Workflow (WorkflowId,WorkflowTypeId,WorkflowTypeServiceName,IsCurrent) VALUES (:WorkflowId,:WorkflowTypeId,:WorkflowTypeServiceName,:IsCurrent);'

/* 61 */   )

/* 62 */   TO OPERATOR

/* 63 */   (

/* 64 */   W_1_o_Workflow[1]

/* 65 */  

/* 66 */   ATTRIBUTES

/* 67 */   (

/* 68 */   UserName = 'xxxxx', 

/* 69 */   UserPassword = 'xxxxx', 

/* 70 */   LogTable = 'DEV_DB_LAND.Workflow_log', 

/* 71 */   TargetTable = 'DEV_DB_LAND.Workflow', 

/* 72 */   TdpId = '172.xx.xx.x'

/* 73 */   )

/* 74 */   )

/* 75 */   SELECT * FROM OPERATOR

/* 76 */   (

/* 77 */   W_0_o_Workflow[1]

/* 78 */  

/* 79 */   ATTRIBUTES

/* 80 */   (

/* 81 */   UserName = 'xxxxxx', 

/* 82 */   UserPassword = 'xxxxx', 

/* 83 */   SelectStmt = 'SELECT WorkflowId,WorkflowTypeId,WorkflowTypeServiceName,IsCurrent FROM DEV_DB.App.Workflow;', 

/* 84 */   DSNName = 'SQL Server MS Client'

/* 85 */   )

/* 86 */   );

/* 87 */  );

Enthusiast

Re: TPT load script fails with incompatible message

Hello,

I am now considering using MLOAD to import data directly from MSSQL to Teradata.

  1. Can MLOAD be used to run a Select statement from MSSQL to import directly to a Teradata table ?
  2. Where could I find an example of that script please ?...

Thank you for your assistance.

Boris.

Teradata Employee

Re: TPT load script fails with incompatible message

No. MLOAD does not support that functionality.

You will need to use TPT.

-- SteveF