If you encounter error Msg 15123, Level 16, reading “The configuration option ‘Agent XPs’ does not exist“, you were probably trying to execute EXEC SP_CONFIGURE 'Agent XPs' while advanced options are hidden.

This error is easily fixed.

Example of the Error

Here’s an example of code that causes this error.

EXEC SP_CONFIGURE 'Agent XPs';

Result:

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

As mentioned, this means that show advanced options are set to 0.

Solution

We can fix the above error by running the following code:

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

Result:

Started executing query at Line 18
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

Started executing query at Line 20
Commands completed successfully.
Total execution time: 00:00:00.126

After the first line ran, it advised us to run RECONFIGURE, which we did.

Now when we run the previous code, we no longer get an error.

EXEC SP_CONFIGURE 'Agent XPs';

Result:

 +-----------+-----------+-----------+----------------+-------------+
 | name      | minimum   | maximum   | config_value   | run_value   |
 |-----------+-----------+-----------+----------------+-------------|
 | Agent XPs | 0         | 1         | 0              | 0           |
 +-----------+-----------+-----------+----------------+-------------+ 

Change a Setting

Probably the reason you were trying to do this in the first place, was that you wanted to change an advanced option (such as to enable the Agent XPs).

Here’s an example of enabling the Agent XPs.

EXEC SP_CONFIGURE 'Agent XPs', 1;
GO
RECONFIGURE;
GO

Result:

Started executing query at Line 23
Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.

Started executing query at Line 25
Commands completed successfully.
Total execution time: 00:00:00.142

Now we can check the setting again.

EXEC SP_CONFIGURE 'Agent XPs';

Result:

 +-----------+-----------+-----------+----------------+-------------+
 | name      | minimum   | maximum   | config_value   | run_value   |
 |-----------+-----------+-----------+----------------+-------------|
 | Agent XPs | 0         | 1         | 1              | 1           |
 +-----------+-----------+-----------+----------------+-------------+ 

Hide Advanced Options

Once finished whatever it is you need to do, it’s a good idea to hide advanced options again.

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