Error 2762

Severity

16

Message text

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

Explanation

SQL commands are grouped into these 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:

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:


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! DDL 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 automatically. 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