Adaptive Server allows some system procedures to run in sessions that use chained transaction mode:
These system procedures can run in sessions using chained transaction mode if there are no open transactions:
sp_configure
sp_engine
sp_rename
These system procedures can run in sessions using chained transactions after you use sp_procxmode to change the transaction mode to anymode:
sp_addengine
sp_dropengine
sp_showplan
sp_sjobcontrol
sp_sjobcmd
sp_sjobcreate
See the Reference Manual: Procedures.
sp_sjobdrop can run in sessions using chained transaction mode, but fails if you execute it during an open transaction.
When you execute these stored procedures, Adaptive Server implicitly commits the changes performed by these stored procedures when there are no open transactions, so you need not issue a commit or rollback.
If an open transaction exists when you issue:
sp_rename, sp_configure, sp_engine, sp_addengine, or sp_dropengine – the procedures fail with error 17260 because they cannot run within a transaction.
sp_sjobcontrol, sp_sjobcmd, sp_sjobcreate, sp_sjobdrop, or sp_showplan – Adaptive Server leaves the transaction open after the procedure executes. You must explicitly issue commit or rollback for the entire transaction.
If these procedures receive an error when they execute, they roll back only the operations performed inside the procedure. Operations performed before the procedures executed, even if those operations have been performed in the same transaction.
Use set chained {on | off} to set the chained mode for the session. See the Reference Manual: Commands.
