Archives of the TeradataForum

Message Posted: Tue, 10 Aug 2004 @ 20:23:26 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Tuesday, August 10, 2004 16:11 -->

Hi All,

I need to load data that is being extracted from SQL Server using isql -o. I am extracting only 1 CHAR(30) column for load into Teradata. The issue I am having that a CRLF is being written in the first positions of the last line of the file. So the last extracted record is written to the file, with a CRLF in the last two positions of the last record, then the next row contains only a CRLF. See sample extract and MLOAD code, and resulting error below:

Extract File:

     SW CQDCHW001MV5918
     241325919623963783
     SW CQDCHW001MV4551
     SW CQDCHW001MV4732

MLOAD:

     .logtable PRODWK.ODS_SMW_PO_Header_Log;
     .run file ..\bteq\Multiload_Bteqs\logon.bte;

     DATABASE PRODWK;
     .BEGIN IMPORT MLOAD
       TABLES PRODWK.Verify_ODS_SMW_PO_Header
         WORKTABLES  PRODWK.ODS_SMW_PO_Header_WT
         ERRORTABLES PRODWK.ODS_SMW_PO_Header_ET
                     PRODWK.ODS_SMW_PO_Header_UV
       SESSIONS 4
     ;
     .LAYOUT INPUTLAYOUT;
     .FILLER  FILLER1                *  CHAR(01);
     .FIELD   Order_Nb               *  CHAR(30);
     .FILLER  EOL                    *  CHAR(03);

     .DML LABEL INSERTS;
     INSERT INTO PRODWK.Verify_ODS_SMW_PO_Header
      ( Order_Nb )
     VALUES
      (:Order_Nb )
     ;

     .IMPORT INFILE ..\..\Extract_Staging\Orders.out
       LAYOUT INPUTLAYOUT
       FORMAT UNFORMAT
       APPLY INSERTS
     ;
     .END MLOAD;
     .logoff;

MLOAD output:

     **** 15:56:57 UTY0817 MultiLoad submitting the following request:
          CHECKPOINT LOADING;
     **** 15:56:57 UTY0817 MultiLoad submitting the following request:
          USING Ckpt(VARBYTE(1024)) INSERT PRODWK.ODS_SMW_PO_Header_Log
          (Logtype, Seq, MLoadSeq, MLoadImpSeq, MLoadSrcSeq,
          MiscInt1,MiscInt2,MiscInt3,MiscInt4,
          MiscInt5,MiscInt6,MiscInt7,MiscInt8,MLoadCkpt) VALUES (110, 1, 0, 1, 0, 0,
          0, 0, 0, 0, 0, 0, 0, :Ckpt);
     **** 15:56:57 UTY4203 Attempted to access out of range input data in field 'ORDER_NB', file
          '..\..\Extract_Staging\Orders.out',record number '2987'.
     **** 15:56:57 UTY1803 Import processing statistics
          .                                   IMPORT  1   Total thus far
          .                                   =========   ==============
          Candidate records considered:.....       2987.............2987
          Apply conditions satisfied:.......       2986.............2986
          Candidate records not applied:....          0.           ....0
          Candidate records rejected:.......          0...........   ..0

      -----------------------------------------------------------------------
      -                                                                     -
          -          Logoff/Disconnect                                      -
      -                                                                     -
      -----------------------------------------------------------------------

     **** 15:56:57 UTY6212 A successful disconnect was made from the RDBMS.
     **** 15:56:57 UTY2410 Total processor time used = '0.15625 Seconds'
          .       Start : 15:56:48 - TUE AUG 10, 2004
          .       End   : 15:56:57 - TUE AUG 10, 2004
          .       Highest return code encountered = '12'.

I have tried changing APPLY INSERTS to APPLY INSERTS WHERE FILLER1 = ' ';, which seems to me should exclude the record that is only a CRLF, but the MLOAD fails with the same error.

How do I get this load to work? [ I have tried various changes to the extract to eliminate the issue - no success.]

TIA.

	<Prev	Next>		<<First	<Prev	Next>	Last>>