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 2762

Severity

16

Error message text

The '%s' command is not allowed within a multi-statement transaction in the '%.*s' database.

Explanation

SQL commands are grouped into the following categories:

• SQL commands that are not allowed in transactions at all.

• SQL commands, such as Data Definition Language (DDL) commands, that are allowed in transactions only if the required database option (ddl in tran) is set to TRUE.

• SQL commands that are run across databases to create, alter or drop objects in another database, and are allowed in transactions only if the required database option (ddl in tran) is set to TRUE for that database.

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:

• alter database

• create database

• dbcc reindex, dbcc fix_text

• disk init

• drop database

• dump database, dump transaction

• load database, load transaction

• reconfigure

• select into

• set transaction isolation level

• truncate table

• update statistics

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:

• create default, create index, create procedure, create rule, create schema, create table, create trigger, create view

• drop default, drop index, drop procedure, drop rule, drop table, drop trigger, drop view

• grant

• revoke

Action

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:

• Use a permanent object name, so that tempdb is not affected.

• Execute the command outside a multi-statement transaction.

  Warning!

  Using data definition language commands on tempdb within transactions may cause concurrency problems in tempdb. Always leave ddl in tran set to FALSE in tempdb.

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.

Warning!

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.

Additional information

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

All versions