Skip to Content

Rollback transactions and Stored Procedures

Hi. Have anyone else noticed (or is is me that is wrong) that HANA transaction can't rollback create and drop of stored proceures. Example. If you create a Store procedure and call it "MyProcedure" Then run the following SQL in the console of HANA Studio: SET TRANSACTION; DROP PROCEDURE "MyProcedure"; ROLLBACK; I would have imagined that after running this the procedure should still be there but it is not... Bug or Am i doing something wrong?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • avatar image
    Former Member
    Jun 22, 2017 at 08:46 AM

    Hi Rasmus,

    All DDL statements (Create,Drop etc) are Rollback-proof, I mean you can not rollback against a fired DDL statement, because an automatic implicit commit gets fired after a successful DDL statement.

    And It's same for almost all DB vendors.(except PostgreSQL)

    Add comment
    10|10000 characters needed characters exceeded

    • Actually it's not correct. In HANA it depends on the session property (answered below), in SQL Server, for example, there are limitations for just a few object types such as Database, Full Text Index etc.

  • Sep 18 at 08:47 AM

    Hi Rasmus,

    Maybe I'm resurrecting an old thread, but in case someone falls here: By default HANA server implicitly commits all DDL statements: ALTER/DROP/CREATE TABLE/PROCEDURE etc. To change this behaviour there is a session property "TRANSACTION AUTOCOMMIT DDL". If you set it to OFF you can handle these transactions yourself. More information in SAP HELP

    Add comment
    10|10000 characters needed characters exceeded