16
%s command not allowed within multi-statement transaction.
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 allowed only if the transaction affects some other database. These commands include create table, drop table, and other commands that are run across databases to create or drop objects in another database when the database in which the objects are being created or dropped has the database option ddl in tran set to TRUE.
Error 226 occurs when Adaptive Server detects a command that is not allowed in a multi-statement transaction. A multi-statement transaction is a set of commands prefaced with the begin transaction command.
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
select into
set transaction isolation level
truncate table
update statistics
setuser
The following 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
If the 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. Setting ddl in tran to TRUE causes locks on system tables, which can affect performance. You can check the current setting of ddl in tran with sp_helpdb.
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 226 may occur. Refer to the user guide for your application to determine if this is the case.
All versions
