All Forums Database
Ashok Pentapati 20 posts Joined 07/11
07 Nov 2011
Truncation of Data while Bteq Export

Hi All,

I am trying to export data to a file using REPORT format.The data inside the Select statement exceeds 254 characters.Data is getting exported fine till last line.However the last line is getting truncated.I tried to use even ".EXPORT DATA FILE".But the data is getting populated with junk charatcers.I tried even .REPORTWIDE format but of no use.Can some please help me with this.

I used below command for exportnig the data:

--Tried with REPORT format

.EXPORT REPORT FILE=C:\Documents and Settings\copentaa\Desktop\Core Scripts\ERROR HANDLING\RUNTIME_TPT_ERR_HND.sql;

--Tried with DATA format

.EXPORT DATA FILE=C:\Documents and Settings\copentaa\Desktop\Core Scripts\ERROR HANDLING\RUNTIME_TPT_ERR_HND.sql;

--Tried with .REPORTWIDE format

.EXPORT REPORTWIDE FILE=C:\Documents and Settings\copentaa\Desktop\Core Scripts\ERROR HANDLING\RUNTIME_TPT_ERR_HND.sql;

 

Thanks,

Ashok.

Jimm 298 posts Joined 09/07
07 Nov 2011

And you used the .WIDTH command to set the width of the report of course.

If you did, post the script.

 

Ashok Pentapati 20 posts Joined 07/11
07 Nov 2011

Yes Jimm.I used .Set width as well.Below is the script.

 

.set width 50000
.set titledashes off
.set sidetitles OFF
.set format off
.SET RTITLE ''

INSERT INTO  INS_STMNT_EXP
SELECT
'INSERT INTO EDW_WORK.MLOAD_REJECTS_WRK1 SELECT Databasename,Tablename,INDEXNAME,FULINDCOLUMNNAME,'||
FULINDEXVALUE||',DBCErrorCode,DBCErrorField,ErrorText FROM '||
TRIM(DATABASENAME)||'.'||TRIM(TABLENAME)||' A INNER JOIN EXISTIN_INDEX_COMBIN B ON '||'B.INDEXNAME =' ||''''  ||
TRIM(INDEXNAME) || '''' ||'AND B.TABLENAME =' ||''''  || TRIM(TABLENAME) || ''''  ||' INNER JOIN DBC.ERRORMSGS C ON '||' A.DBCErrorCode=C.ErrorCode;'
FROM EXISTIN_INDEX_COMBIN WHERE TABLENAME LIKE '%_ERR1';

INSERT INTO INS_STMNT_EXP
SELECT
'INSERT INTO EDW_WORK.MLOAD_REJECTS_WRK1 SELECT Databasename,Tablename,INDEXNAME,FULINDCOLUMNNAME,''Data Not yet loaded to determine index'',A.ErrorCode,A.ErrorField,ErrorText FROM '||
TRIM(DATABASENAME)||'.'||TRIM(TABLENAME)||' A INNER JOIN EXISTIN_INDEX_COMBIN B ON '||'B.INDEXNAME =' ||''''  ||
TRIM(INDEXNAME) || '''' ||'AND B.TABLENAME =' ||''''  || TRIM(TABLENAME) || ''''  ||' INNER JOIN DBC.ERRORMSGS C ON '||' A.ErrorCode=C.ErrorCode;'
FROM EXISTIN_INDEX_COMBIN WHERE TABLENAME LIKE '%_ERR';

.EXPORT REPORTWIDE FILE=C:\Documents and Settings\copentaa\Desktop\Core Scripts\ERROR HANDLING\RUNTIME_TPT_ERR_HND.sql;
SELECT CAST(INS_STM_EXP_COL AS VARCHAR(50000)) (TITLE '') FROM INS_STMNT_EXP;

.RUN FILE =C:\Documents and Settings\copentaa\Desktop\Core Scripts\ERROR HANDLING\RUNTIME_TPT_ERR_HND.sql;

Thanks,

Ashok.

Ashok.Pentapati 16 posts Joined 06/09
07 Nov 2011

Hi ,

Can somebody please help me out with this?

Thanks.

Ashok.Pentapati 16 posts Joined 06/09
08 Nov 2011

Is there anyone who can help with this please?

Thanks.

Ashok.Pentapati 16 posts Joined 06/09
11 Nov 2011

Can someone please help me out how to get the proper data without truncation and junk characters while bteq export???

milind.bengeri 4 posts Joined 02/12
24 Feb 2012

I am also facing the same problem.

Did you get an answer Ashok?

rajanimandava 4 posts Joined 01/11
28 Mar 2013

Can you please try the below
 
.EXPORT RESET

You must sign in to leave a comment.