16
The '%s' command is not allowed within a multi-statement transaction in the '%.*s' database.
SQL commands are grouped into these 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
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.
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.
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.
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.
All versions
