Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 12 Oct 2005 @ 18:02:58 GMT


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


Subj:   BTEQ - Macros inside transactions
 
From:   Anomy Anom

<-- Anonymously Posted: Wednesday, October 12, 2005 11:23 -->

I want to be able to enclose a call to a macro and regular SQL inside one BT/ET statement. This should allow rollback if the macro fails or the sql fails.

When running a macro and sql inside a BT/ET in SQL assistant or Atanasoft it works as I would expect. When the macro or insert fail the transaction rolls back.

     BEGIN TRANSACTION;

     exec macrotest ( 43213);
     insert into test values ('10',10);

     END TRANSACTION;

My problem is that BTEQ handles the exact same command DIFFERENTLY!

     .set session trans BTET;
     .logon dbc/,};

     BEGIN TRANSACTION;

     exec macrotest ( 43213);
     insert into test values ('10',10);

     END TRANSACTION;
     EXIT;

Basically when the macrotest macro fails it is not rolling back the entire transaction. The insert goes ahead as usual..

     .set session trans BTET;
     +---------+---------+---------+---------+---------+---------+

     .logon dbc/edw_dev_dba,

      *** Logon successfully completed.
      *** Transaction Semantics are BTET.
      *** Character Set Name is 'ASCII'.

      *** Total elapsed time was 1 second.

     +---------+---------+---------+---------+---------+---------+
     .errorout stdout
      *** Error messages now directed to STDOUT.
     +---------+---------+---------+---------+---------+---------+
     BEGIN TRANSACTION;

      *** Begin transaction accepted.
      *** Total elapsed time was 1 second.


     +---------+---------+---------+---------+---------+---------+

     exec edw_dev_ld_tbls.macrotest ( 43213);
      *** Failure 2801 Duplicate unique prime key error in
     EDW_DEV_LD_TBLS.key_gen.
                     Statement# 5, Info =0
      *** Total elapsed time was 1 second.


     +---------+---------+---------+---------+---------+---------+
     insert into edw_dev_ld_tbls.test values ('10',10);

      *** Insert completed. One row added.
      *** Total elapsed time was 1 second.


     +---------+---------+---------+---------+---------+---------+

     END TRANSACTION;
      *** Failure 3510 Too many END TRANSACTION statements.
                     Statement# 1, Info =0
      *** Total elapsed time was 1 second.


     +---------+---------+---------+---------+---------+---------+
     EXIT;

Thanks



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023