sp_procxmode

Description

Displays or changes the execution modes associated with stored procedures.

Syntax

sp_procxmode [procname [, tranmode]]

Parameters

procname

is the name of the stored procedure with the transaction mode you are examining or changing.

tranmode

is the new execution mode for the stored procedure. Values are "chained", "unchained", and "anymode", for transaction modes, and '[No] Dynamic Ownership Chain'.

Examples

Example 1

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

Example 2

Displays the transaction mode of the stored procedure byroyalty:

sp_procxmode byroyalty
procedure name                   transaction mode 
------------------------------   ---------------- 
byroyalty                        Unchained 

Example 3

Changes the transaction mode for the stored procedure byroyalty in the pubs2 database from “unchained” to “chained”:

sp_procxmode byroyalty, "chained"

Usage

Permissions

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.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

See also

Commands begin transaction, commit, save transaction, set