Use sp_procxmode to display or change the transaction mode of stored procedures.
sp_procxmode byroyalty, "chained"
sp_procxmode “anymode” lets stored procedures run under either chained or unchained transaction mode. For example:
sp_procxmode byroyalty, "anymode"
Use sp_procxmode without any parameter values to display the transaction modes for all stored procedures in the current database:
sp_procxmode
procedure name transaction mode ------------------------- -------------------- byroyalty Any Mode discount_proc Unchained history_proc Unchained insert_sales_proc Unchained insert_salesdetail_proc Unchained storeid_proc Unchained storename_proc Unchained title_proc Unchained titleid_proc Unchained
You can use sp_procxmode only in unchained transaction mode.
To change a procedure’s transaction mode, you must be a system administrator, the database owner, or the owner of the procedure.