SQL SERVER – FIX : Error: Msg 15123, Level 16 – The configuration option ‘advance option’ does not exist, or it may be an advanced option.

I received another email describing error received due to my executing script from my previous article .

Error :
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option ‘optimize for ad hoc workloads’ does not exist, or it may be an advanced option.

Let us quickly see the reproduction of this error in following image.

Fix/Workaround/Solution:
The reason this error is happening because of not enabling advance option. Run complete following script and it should fix the problem.

sp_CONFIGURE 'show advanced options',1
RECONFIGURE
GO

sp_CONFIGURE ‘optimize for ad hoc workloads’,1
RECONFIGURE
GO

Reference : Pinal Dave (http://blog.sqlauthority.com)

About these ads

6 thoughts on “SQL SERVER – FIX : Error: Msg 15123, Level 16 – The configuration option ‘advance option’ does not exist, or it may be an advanced option.

  1. This is for SQL Server 2008 only, just want to let readers know, not 2005

    —————————————————————————————————————————————————————————————————————————————————————-
    Microsoft SQL Server 2005 – 9.00.3042.00 (X64)
    Feb 10 2007 00:59:02
    Copyright (c) 1988-2005 Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

    Configuration option ‘show advanced options’ changed from 1 to 1. Run the RECONFIGURE statement to install.
    Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51

    The configuration option ‘optimize for ad hoc workloads’ does not exist, or it may be an advanced option.

    Like

  2. [Repost for email notification]
    So, what do we do to enable distributed ad hoc in 2005? Is the Surface Area Config Wizard the only option?

    Like

  3. This is Ok, But still I get eroor :

    Server: Msg 15123, Level 16, State 1, Procedure sp_configure, Line 79
    The configuration option ‘xp_cmdshell’ does not exist, or it may be an advanced option.

    What should i do for enable xp_cmdshell sp.
    Notice that I am user of sql server 2000 not DBA, I think its reason for that, is it?

    Like

  4. ‘Ad Hoc Distributed Queries’ entry not available in the sysconfigures master table in sql 2000 server how to solve this issue.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s