Ad hoc update to system catalogs is not supported

Exec sp_configure 'show advanced options', 1 reconfigureexec sp_configure 'ad hoc distributed queries ', 1 reconfigure: configuration option 'show advanced options' changed from 1 to 1. run the reconfigure statement to install. MSG 5808, level 16, state 1, line 49ad hoc update to system catalogs is not supported. if it is changed to the following, the following error will not be reported: exec sp_configure 'show advanced options', 1 reconfigure with overrideexec sp_configure 'ad hoc distributed queries ', and 1 reconfigure with override:
Configuration option 'show advanced options' changed from 1 to 1. run the reconfigure statement to install. configuration option 'ad hoc distributed queries 'changed from 1 to 1. run the reconfigure statement to install.

The online documentation I read explains the differences between reconfigure and reconfigure with override as follows:

Reconfigure and reconfigure with override are valid for each configuration option. However, the basic reconfigure statement rejects any option values that are out of a reasonable range or may cause an option conflict. For example, ifRecovery IntervalThe value is greater than 60 minutes, orAffinity maskAndAffinity I/O maskReconfigure will generate an error. In contrast, reconfigure with override accepts any option value with the correct data type and forces reconfiguration with the specified value.

Note: an inappropriate option value will adversely affect the configuration of the server instance. Use reconfigure with override with caution.

In addition, we need to know the following information about reconfigure:

The reconfigure statement can dynamically update certain options, while the update of other options requires the server to be stopped and then restarted. For example, the database engine dynamically updatesMin Server MemoryAndMax Server MemoryServer Memory options, so you can change these two options without restarting the server. In contrast, reconfigurationFill FactorYou must restart the database engine.

After you run reconfigure on a configuration option, you can runSp_configure'Option_name'To check whether this option has been dynamically updated. For dynamic update options,Run_valueColumn andConfig_valueThe column value should match. You can also viewSYS. configurationsDirectory ViewIs_dynamicColumn to determine which options are dynamic.

Note: If the value specified for the option is too highRun_valueThe column will reflect the fact that the database engine uses dynamic memory by default, instead of invalid settings.

 

Ad hoc update to system catalogs is not supported