Displaying current values
When executed without parameters, sp_configure shows configurable settings along with their allowed value range and currently set value. Depending on the value of show advanced options, advanced options are included or excluded from the result set.
The names and values of such settings can alternatively be queried from
sys.configurations.
Changing a setting
exec sp_configure 'clr enabled'
-- name minimum maximum config_value run_value
-- ----------------------------------- ----------- ----------- ------------ -----------
-- clr enabled 0 1 0 0
exec sp_configure 'clr enabled', 1
--
-- Configuration option 'clr enabled' changed from 0 to 1. Run the RECONFIGURE statement to install.
exec sp_configure 'clr enabled'
-- name minimum maximum config_value run_value
-- ----------------------------------- ----------- ----------- ------------ -----------
-- clr enabled 0 1 1 0
reconfigure
--
-- Commands completed successfully.
exec sp_configure 'clr enabled'
-- name minimum maximum config_value run_value
-- ----------------------------------- ----------- ----------- ------------ -----------
-- clr enabled 0 1 1 1
Msg 15123… The configuration option '…' does not exist, or it may be an advanced option.
Some options can only be changed if the option show advanced options is set to 1. This is demonstrated with the following statements.
First, we set show advanced options to 0:
exec sp_configure 'show advanced options', 0
reconfigure;
Then we try to change the clr srict security option. Because show advanced options is turned off, it throws an error:
exec sp_configure 'clr strict security', 0;
-- Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62 …
-- The configuration option 'clr strict security' does not exist, or it may be an advanced option.
After enabling show advanced options, the clr strict security option can also be changed:
exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'clr strict security', 0;
reconfigure