Adaptive Server versions 15.7 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
- 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, but do not roll back the operations performed before they execute, even though the operations are performed in the same transaction.
Use set chained {on | off} to set the chained mode for the session.
See the Reference Manual: Commands and the Reference Manual: Procedures.