If you’re trying to check an advanced configuration option in SQL Server, but you get an error, you might find the following helpful.

Example

For example, if when running the following code to check the Database Mail XPs configuration values:

EXEC sp_configure 'Database Mail XPs'

You get the following error:

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62
The configuration option 'Database Mail XPs' does not exist, or it may be an advanced option.

You need to set the show advanced options server configuration option to 1.

Set show advanced options to 1

Here’s how to set show advanced options to 1:

sp_configure 'show advanced options', 1;  
GO
RECONFIGURE;  
GO

Result:

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Commands completed successfully.
Commands completed successfully.

That’s all. You have now set show advanced options to 1.

The RECONFIGURE statement updates the value, and specifies that if the configuration setting does not require a server restart, the currently running value should be updated.

Fortunately, the show advanced options doesn’t require a server restart.

Test the Result

Now I can successfully check the Database Mail XPs configuration values:

EXEC sp_configure 'Database Mail XPs'

Result:

+-------------------+-----------+-----------+----------------+-------------+
| name              | minimum   | maximum   | config_value   | run_value   |
|-------------------+-----------+-----------+----------------+-------------|
| Database Mail XPs | 0         | 1         | 0              | 0           |
+-------------------+-----------+-----------+----------------+-------------+

In this case, the Database Mail XPs option is disabled (set to 0). It would need to be set to 1 in order for it to be enabled.

Hide show advanced options

Once you’ve done what you need to do, you might want to set show advanced options back to its default value of 0 (which hides the advanced options):

sp_configure 'show advanced options', 0;  
GO
RECONFIGURE;  
GO

Caution

Microsoft recommends that advanced options should be changed only by an experienced database administrator or certified SQL Server technician.