Rollback transactions and Stored Procedures

[rasmuswulff_jensen]

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?

[Former Member]

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)

[former_member390407]

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.

[former_member390407]

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