Bugzero one-for-all bug tracking and help desk support system
Q: Sybase, error when dropping/creating tables: ... command not allowed within multi-statement transaction in the 'db_name' database. at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2846) at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:2168) at com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69) at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:220) at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:203) at com.sybase.jdbc2.jdbc.SybStatement.queryLoop(SybStatement.java:1596) at com.sybase.jdbc2.jdbc.SybCallableStatement.executeQuery(SybCallableStatement.java:102) at com.sybase.jdbc2.jdbc.SybDatabaseMetaData.executeQuery(SybDatabaseMetaData.java:2379) at com.sybase.jdbc2.jdbc.SybDatabaseMetaData.getColumns(SybDatabaseMetaData.java:1983) A: If you encounter this error, you can set the database option of "ddl in tran" to true. Sybase command line: use db_name go sp_dboption db, "ddl in tran", true go Or, use Interactive SQL (isql) to issue the following command: sp_dboption db_name, "ddl in tran", true Here, "db_name" is your database name, such as bugzero_db. The following is from Sybase manual http://manuals.sybase.com/onlinebooks/group-as/asg1250e/svrtsg/@Generic__BookTextView/34899
Error message text
The '%s' command is not allowed within a multi-statement transaction in the '%.*s' database.
SQL commands are grouped into the following categories:
Error 2762 typically occurs in the context of data definition commands such as creating, altering, or dropping objects. It occurs when Adaptive Server detects a command that is not allowed in a multi-statement transaction for the specified database. A multi-statement transaction is a set of commands prefaced with the begin transaction command, or when chained mode is on.
Error 226 is a similar error that may be raised along with Error 2762. While Error 2762 typically involves commands that are run across databases, Error 226 is raised when the command affects only the local database.
The following commands are never allowed in multi-statement transactions:
The following DDL commands are not normally allowed in multi-statement transactions but you can use them if you use sp_dboption to set ddl in tran to TRUE first:
The literal `%.*s' in the error message is the name of the database specified in the command. Note the database name before choosing any corrective action.
For Commands Involving tempdb Database
Error 2762 can be raised when a command creates or drops temporary objects (objects in tempdb) within the context of a multi-statement transaction. For example, this code may generate a 2762 error:
1> begin transaction 2> create table #cities 3> (city_name char(15) not null) 4> commit transaction 5> go
The error is raised when ddl in tran is set to FALSE in tempdb. Use one of the following strategies to correct this error:
For Commands Not Involving tempdb Database
If the DDL command is allowed in a multi-statement transaction when ddl in tran is set to TRUE, set ddl in tran to TRUE before running the transaction. You can check the current setting of ddl in tran with sp_helpdb.
Data definition language commands hold locks on system tables such as sysobjects and this can affect performance. Avoid using them inside transactions; if you must use them, keep the transactions short.
If the command is never allowed in a multi-statement transaction, execute it outside the multi-statement transaction.
Some applications take SQL statements as input and run them for you. If the application uses begin and commit or rollback transaction to surround those statements, Error 2762 may occur. Refer to the user guide for your application to determine if this is the case.
Versions in which this error is raised