In the earlier post Error 2762 The ‘xxx’ command is not allowed within a multi-statement transaction in the database, we covered how to find a work-around for solving multi-statement transaction error and keep it running while having option of rolling back or committing it based on the requirement and execution.
Today, we will talk about similar error – at first glance, it looks almost same and is difficult to distinguish unless and until look at it carefully.
Both of them contains same line as “command is not allowed within a multi-statement transaction” however if you look at it closely – The first one contains database name whereas the later one is more generic and doesn’t matter which database are you running it from and it doesn’t have database details.
Lets see few commands where error code is 226 and not 2762:
In above screenshot, as you can see – we are altering the table and removing one column, which throws error code 226. You might be wondering, may be “ddl in tran” option is not enabled for this database. to showcase that, below screen is for the reference with db option details:
Lets see one more example of such type as below:
Here we are trying to truncate table within a transaction and we encounter same error i.e. 226.
Lets see one more example of this type:
In the above example, we are performing bulk insert by doing select into command and even that one is not allowed within a transaction and we get same error code 226.
Error code 226 doesn’t have work-around and can’t be solved as SAP Sybase ASE has designed it in this way, I would have been happier if the error message was something like “Below command can’t be executed within Transaction”.
From these two error messages, we conclude that there are two types of transactions – one are those which could be rolled back and can be kept inside a transaction if we enable ddl in tran option at database level whereas there is another set of transaction, which is independent of database and can’t be executed at all within a transaction.
You may read more about these at SAP Sybase official pages:
Please leave your comment if you liked this post or have any feedback.