All Forums Teradata Applications
rohit.lalwani1 8 posts Joined 08/13
13 Aug 2013
How to handle failure statement in Bteq ?

Hi ,
I am trying to create a bteq  script which run multiple Insert statements through sql file. 
Supose i i have a file like abc.sql, In that i hv 10 Insert statements like
(Insert .... ;
Insert... ;)
now i run this file in Bteq using .RUN FILE abc.sql
my concern is if any of the insert statement failed for any reason (like syntactical issue ,spool issue,TDWM exception issue) the process will not stop and keep running till the last statement in the file.  So if there is any 1 insrt  failed still  i hv 9 records in my table.. 
Any Suggestion will be helpful Thanks in Advance.. 

dnoeth 4628 posts Joined 11/04
13 Aug 2013

You might add .IF ERRORCODE <> 0 THEN .QUIT after each insert or .SET MAXERROR = 1; before .RUN FILE.
Both will stop after the first error.
If you want to run this as all or nothing you must add use a transaction:




CarlosAL 512 posts Joined 04/08
13 Aug 2013

I think the answer is the opposite (e.g.: default bteq behaviour). The OP wants the inserts NOT to stop on error...

dnoeth 4628 posts Joined 11/04
13 Aug 2013

Hi Carlos,
the default in BTEQ is to ignore any error and go on with processing, only SQL Assistant stops by default :-)


CarlosAL 512 posts Joined 04/08
13 Aug 2013

Yep, I knew that ;-)
I misunderstood the OP. I thought he wanted NOT to stop the inserts.
If that's not the case, it may be worth to take a look at 'SET RETRY OFF' and 'SET REPEATSTOP ON' (in case of inserting rows from a file).

vishaman 16 posts Joined 05/13
23 Jul 2014

I have series of explain DDL statment in a bteq file ,when i run this file ,it errors out after explaining some DDL's 

*** Error: reading input, unable to find the end of a request.

     On line 1357, the request has exceeded the maximum

     of 1048500 bytes.

 *** The input is flushed.



 *** Exiting BTEQ...

 *** RC (return code) = 8


No matter what i do ,it still keeps coming out , I tried it without setting any bteq enviornment ( with no .set command)

I even tried  ....


but it keeps coming out with the same error can i make bteq ignore the error and move to the next step ? and can i possibel redirect the failed statement to a different file ?


By the way ,this is how my script now looks like  ...



 set -vx


bteq << EOF >> $LOGFILE 2>&1

.logon tpId/user,Passwd


.run file= final_file.bteq;





You must sign in to leave a comment.