Displays or changes the transaction modes associated with stored procedures.
sp_procxmode [procname [, tranmode]]
is the name of the stored procedure whose transaction mode you are examining or changing.
is the new transaction mode for the stored procedure. Values are "chained", "unchained", and "anymode".
Displays the transaction mode for all stored procedures in the current database:
sp_procxmode
procedure name user name transaction mode ------------------ --------- ---------------- byroyalty dbo Unchained discount_proc dbo Unchained history_proc dbo Unchained insert_sales_proc dbo Unchained insert_detail_proc dbo Unchained storeid_proc dbo Unchained storename_proc dbo Unchained title_proc dbo Unchained titleid_proc dbo Unchained
Displays the transaction mode of the stored procedure byroyalty:
sp_procxmode byroyalty
procedure name transaction mode ------------------------------ ---------------- byroyalty Unchained
Changes the transaction mode for the stored procedure byroyalty in the pubs2 database from “unchained” to “chained”:
sp_procxmode byroyalty, "chained"
To change the transaction mode of a stored procedure, you must be the owner of the stored procedure, the owner of the database containing the stored procedure, or the System Administrator. The Database Owner or System Administrator can change the mode of another user’s stored procedure by qualifying it with the database and user name. For example:
sp_procxmode "otherdb.otheruser.newproc", "chained"
To use sp_procxmode, turn off chained transaction mode using the chained option of the set command. By default, this option is turned off.
When you use sp_procxmode with no parameters, it reports the transaction modes of every stored procedure in the current database.
To examine a stored procedure’s transaction mode (without changing it), enter:
sp_procxmode procname
To change a stored procedure’s transaction mode, enter:
sp_procxmode procname, tranmode
When you create a stored procedure, Adaptive Server tags it with the current session’s transaction mode. This means:
You can execute “chained” stored procedures only in sessions using chained transaction mode.
You can execute “unchained” stored procedures only in sessions using unchained transaction mode.
To execute a particular stored procedure in either chained or unchained sessions, set its transaction mode to “anymode”.
If you attempt to run a stored procedure under the wrong transaction mode, Adaptive Server returns a warning message, but the current transaction, if any, is not affected.
Only a System Administrator, the Database Owner, or the owner of a procedure can execute sp_procxmode to change the transaction mode. Any user can execute sp_procxmode to display the transaction mode.
Commands begin transaction, commit, save transaction, set
