While deploying the code from development team or support team or vendor provided code, you would have encountered below error and would have thought why this message and why to perform DDL statement inside a user transaction:
The ‘%s’ command is not allowed within a multi-statement transaction in the ‘%.*s’ database.
Lets understand why this error pops up before we understand how to solve it. If we try any DDL command within a user transaction that’s when this error comes up as below:
Now, lets see what are the db options which is enabled for our DB(Test_DB) as below:
Next step would be how to solve this issue by changing some configuration option instead of getting back to development team, support team or vendor. This could be solved by using db option as below:
Once above change is done, same can be verified using earlier command as follows:
After verifying above, we can run same command which had failed earlier and it would be successful this time:
Its worth mentioning that if you issue a rollback command post execution of DDL command, the DDL command will be reverted and if it was table creation, table won’t exist.
It would be great to cover other aspect of same statement in the temp table – Please note that, it could be default tempdb or another user defined tempdb.
As you can see from above screenshot, ddl in tran is not enabled for tempdb database, we can enable this using same command as it was done earlier:
Sybase ASE gives you a legitimate warning saying that the option should be changed for all the temprary databases as allocation of tempdb is dynamic and if the db options are not consistent, application could behave in a vaguely manner.
Here in the above screenshot, I have changed the option for both of my temporary databases.
Please leave your comment if you liked this post or have any feedback.