Post reply

Need to enable Ad Hoc Distributed Queries option

  • July 20, 2006 at 4:52 pm

    #113701

    Hi,

    I need the "Ad Hoc Distributed Queries" feature (for the SQLServer installation of my service provider) which is disabled by default.  I read BOL and came up with this:

    EXEC sp_configure 'show advanced options',

    GO

    RECONFIGURE

    GO

    EXEC sp_configure 'Ad Hoc Distributed Queries', 1

    GO

    I am new to SQL Server and want to make sure this is correct.  Is there any special account that I should run this from?  We have a new ERP system going in and the consultants are trying to install and populate a database on our SQL Server 2005 box and are getting errors that Ad Hoc Distributed Queries option needs to be enabled.

    Thanks!

    Isabelle

    Thanks!
    Bea Isabelle

  • July 21, 2006 at 6:26 am

    #650645

    The account running this will have to be a member of sysadmin.  Other than that, it should be fine.

    John

  • July 21, 2006 at 8:19 am

    #650724

    This works, even though it is different from SQL 2000.

    It will tell you if you need to reconfigure, and give you a success when it is done

  • July 21, 2006 at 9:10 am

    #650742

    Hi,

    I am logged in as the admin account and get this error:

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near ','.

    Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51

    The configuration option 'Ad Hoc Distributed Queries' does not exist, or it may be an advanced option.

    This is a SQL 2005 database on a 2003 server sp1.  Is there anything else I'm supposed to to?

    Isabelle

    Thanks!
    Bea Isabelle

  • July 21, 2006 at 9:26 am

    #650752

    Hi,

    I got it to work by running the following:

    EXEC

    sp_configure 'show advanced options', 1

    GO

    RECONFIGURE

    GO

    EXEC

    sp_configure 'Ad Hoc Distributed Queries', 1

    GO

    I got the message

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

    Configuration option 'Ad Hoc Distributed Queries' changed from 0 to 1. Run the RECONFIGURE statement to install

    Then I typed: RECONFIGURE and got the message:

    Command(s) completed successfully.

     Is this correct?  Do I have to do anything else to change the 'show advanced options back to 0?

    Isabelle

    Thanks!
    Bea Isabelle

  • July 21, 2006 at 10:51 am

    #650800

    Is there some good reason for not using the Surface Area Configuration tool? 

    It looks like your sp_configure should work, I tried "sp_configure 'Ad Hoc Distributed Queries', 1" on my SQL 2005 server and it worked fine, but if you are having trouble try the SAC tool.

  • September 5, 2007 at 1:33 am

    #731409

    Hi Friends,

      EXEC sp_configure 'show advanced options', 1

    GO

    RECONFIGURE

    GO

    EXEC

    sp_configure 'Ad Hoc Distributed Queries', 1

    GO

    RECONFIGURE

    After executing sp_configure option for AdHoc Distributed Queries you have execute reconfigure command...

    Surface configuration Tool is an additional user friendly option provided by microsoft to enable our add on functionalities.

     

     

    Thanks and Regards,
    Venkatesan Prabu, 😛
    My Blog:

    http://venkattechnicalblog.blogspot.com/

  • December 4, 2007 at 7:36 am

    #756934

    Hello,

    I have runed what you have writen but I got this error message:

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

    Server: Msg 15123, Level 16, State 1, Procedure sp_configure, Line 79

    The configuration option 'Ad Hoc Distributed Queries' does not exist, or it may be an advanced option.

    Valid configuration options are:

    Do you know what does this mean?

  • March 21, 2008 at 4:24 pm

    #792745

    I'm having this exact same problem. Word for word with the query and the error.

    Any help is appreciated.

  • November 20, 2012 at 8:11 am

    #1560824

    I know this is long dead,

    but to do this, you have to enable show advanced options first, and then try setting the value.

    sp_configure 'show advanced options', 1

    reconfigure with override

    sp_configure 'Ad Hoc Distributed Queries', 1

    reconfigure with override

    This being said; seeing as you state you're a new dba, please find out why they want this option enabled; and understand what the implications are before going forward.

  • October 16, 2015 at 7:36 am

    #1833810

    I'm very new to this and I got the same message.

    Where di you type reconfigure into to fix the problem?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply