|
Archives of the TeradataForumMessage Posted: Wed, 12 Oct 2005 @ 18:02:58 GMT
<-- 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/ 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
| ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
Copyright 2016 - All Rights Reserved | ||||||||||||||||||||||||||||||||||||||||||||||||
Last Modified: 15 Jun 2023 | ||||||||||||||||||||||||||||||||||||||||||||||||