none
Enable Ad Hoc Distributed Queries component of SQL Server 2008

    Question

  • Hi,

    I want to make enable Ad Hoc Distributed Queries component of SQL Server 2008.

    Can anybody please help me to do the same ?

    I would be appreciate for any help .

    Thanks in advance !


    Prashant Mhaske

    Thursday, April 05, 2012 6:21 AM

Answers

  • Nope Prashant, Both are the options... GUI and Code See above mentioned post of mine, Hope it will help.

    Feel free to post if any other assistance required.

    Friday, April 06, 2012 2:25 PM

All replies

  • http://msdn.microsoft.com/en-us/library/ms187569.aspx

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Thursday, April 05, 2012 6:38 AM
  • Run it

    sp_configure 'show advanced options', 1;
    RECONFIGURE;
    sp_configure 'Ad Hoc Distributed Queries', 1;
    RECONFIGURE;
    GO


    ------------------ Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Maqbool.

    • Proposed as answer by dbman63 Tuesday, November 27, 2012 7:29 PM
    Thursday, April 05, 2012 6:45 AM
  • You Can achive this by two options –

    1. T-SQL As mentioned Below
    2.  GUI, By using Surface area configuration.

    sp_configure 'show advanced options', 1;

    RECONFIGURE;

    sp_configure 'Ad Hoc Distributed Queries', 1;

    RECONFIGURE;

    GO                                                                                                                     GUI As mentioned Below in the Image

    Thursday, April 05, 2012 6:49 AM
  • Prashant,

     Please refer http://msdn.microsoft.com/en-us/library/ms187569(v=sql.100).aspx


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, April 05, 2012 6:50 AM
  • Hi Praveen,

    Actually i am not getting Sql Serve surface area configuration optio in 2008 R2.

    Can you please help me to open this window ? Do you have used any command to open this window ?

    In 2005 , i can see this option but in 2008 R2 , i am not getting .

    Thanks !


    Prashant Mhaske

    Thursday, April 05, 2012 2:08 PM
  • There is NO Sql Server Surface Area Configuration tool in 2008


    Addie


    • Edited by Addie123 Thursday, April 05, 2012 2:43 PM change
    Thursday, April 05, 2012 2:42 PM
  • Hi Praveen,

    Actually i am not getting Sql Serve surface area configuration optio in 2008 R2.

    Can you please help me to open this window ? Do you have used any command to open this window ?

    In 2005 , i can see this option but in 2008 R2 , i am not getting .

    Thanks !


    Prashant Mhaske

         Prashant,

         In Sql Server 2008 R2, just right click on your Server and select FACETS,  one dialog will appear, there you need to select Surface area configuration.

       Please refer http://www.mssqltips.com/sqlservertip/1673/where-is-the-surface-area-configuration-tool-in-sql-server-2008/

         


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • Edited by Manish_BI Thursday, April 05, 2012 2:58 PM
    Thursday, April 05, 2012 2:57 PM
  • then only query is the option to enable Ad Hoc Distributed component option ?

    Prashant Mhaske

    Thursday, April 05, 2012 3:00 PM
  • Hi Prashant, 

    Follow the below process > 

    Start > All Programs > Microsoft SQL Server 2005 > Configuration Tool > Then Click on "Surface Area Configuration ">

    Click Surface Area Configuration for Features > Then you will be able to see the window as display earlier.

     For SQL Server 2008 - 


    Select Server Configuration from Drop Down...You will see the option just expand the Drop down and select ...........and Bingo. You are Done ;)

    PS- You can use the T-SQL Code in both cases it will work--

    sp_configure 'show advanced options', 1;RECONFIGURE; 
    go 
    sp_configure 'Ad Hoc Distributed Queries', 1;RECONFIGURE; 
    GO


    Then Run the sp_configure  you can see the results like..

    Hope It will help.

    Cheers

    Praveen

    Mark as answer if it answer your question. 


    Friday, April 06, 2012 2:23 PM
  • Nope Prashant, Both are the options... GUI and Code See above mentioned post of mine, Hope it will help.

    Feel free to post if any other assistance required.

    Friday, April 06, 2012 2:25 PM
  • Thanks Preveen !

    Prashant Mhaske

    Sunday, April 08, 2012 10:41 AM