Archives of the TeradataForum

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

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

<-- 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>>