Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need to enable Ad Hoc Distributed Queries option Expand / Collapse
Author
Message
Posted Thursday, July 20, 2006 4:52 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, January 03, 2014 10:42 AM
Points: 1,130, Visits: 487

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
Post #296090
Posted Friday, July 21, 2006 6:26 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 20, 2013 10:02 AM
Points: 1,547, Visits: 275

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

John




Post #296214
Posted Friday, July 21, 2006 8:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 24, 2010 1:57 PM
Points: 182, Visits: 65

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

Post #296302
Posted Friday, July 21, 2006 9:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, January 03, 2014 10:42 AM
Points: 1,130, Visits: 487

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
Post #296328
Posted Friday, July 21, 2006 9:26 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, January 03, 2014 10:42 AM
Points: 1,130, Visits: 487

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
Post #296339
Posted Friday, July 21, 2006 10:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 12:24 PM
Points: 2,835, Visits: 1,124

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.




Post #296389
Posted Wednesday, September 05, 2007 1:33 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, April 15, 2012 7:06 AM
Points: 122, Visits: 205

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, :P
My Blog:

http://venkattechnicalblog.blogspot.com/
Post #396362
Posted Tuesday, December 04, 2007 7:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 04, 2007 7:46 AM
Points: 1, Visits: 3
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?
Post #429292
Posted Friday, March 21, 2008 4:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 25, 2008 5:36 PM
Points: 1, Visits: 4
I'm having this exact same problem. Word for word with the query and the error.

Any help is appreciated.
Post #473140
Posted Tuesday, November 20, 2012 8:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 10:30 AM
Points: 127, Visits: 434
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.



Post #1386936
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse