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.
SQL SERVER - FIX : Error: Msg 15123, Level 16 - The configuration option 'advance option' does not exist, or it may be an advanced option. avaderror1

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.

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

sp_CONFIGURE 'show advanced options',1
RECONFIGURE
GO

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

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

, ,
Previous Post
SQL SERVER – Fix : Error : Msg 4621, Level 16, State 10 : Permissions at the server scope can only be granted when the current database is master
Next Post
SQLAuthority News – Author Video Interview Published Online – Microsoft MVP Summit 2009

Related Posts

11 Comments. Leave new

  • 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.

    Reply
  • [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?

    Reply
  • 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?

    Reply
  • Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near ‘‘’.

    I thought this was sqlauthority!?

    Reply
  • Thank you, it worked for me

    Reply
  • mohandoss krishnan
    November 21, 2013 8:34 am

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

    Reply
  • Gracias estimado.
    Siempre es de mucha ayuda tus posts…

    Reply
  • still same error..

    Reply
  • Before trying to change the advanced option, you need to enable the advanced option by running this query. The query works for SQL Server 2012 and 2014 versions.

    sp_configure ‘show advanced options’, 1;
    GO
    RECONFIGURE;
    GO
    sp_configure ‘Database Mail XPs’, 1;
    GO
    RECONFIGURE;
    GO

    Reply
  • Hi,

    I get this error ‘ Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78 [Batch Start Line 1]
    The configuration option ‘external scripts enabled’ does not exist, or it may be an advanced option.’

    when I run that code in sql server 2017

    sp_configure
    exec sp_configure ‘external scripts enabled’,1
    RECONFIGURE WITH OVERRIDE

    sp_CONFIGURE ‘show advanced options’,1
    RECONFIGURE
    GO

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

    so basically i have installed sql server 2017 for machine learning services through python. but using my previous database which is in sql server 2008 management studio. not sure i am doing right or not.

    Can someone help me out please?

    Reply

Leave a Reply

Menu