Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need to enable Ad Hoc Distributed Queries option


Need to enable Ad Hoc Distributed Queries option

Author
Message
Isabelle2378
Isabelle2378
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1165 Visits: 498

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
laker_42
laker_42
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1557 Visits: 275

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

John





Eric Peterson
Eric Peterson
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 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


Isabelle2378
Isabelle2378
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1165 Visits: 498

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
Isabelle2378
Isabelle2378
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1165 Visits: 498

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
Scott Coleman
Scott Coleman
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2944 Visits: 1417

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.





Venkatesan Prabu
Venkatesan Prabu
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 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, Tongue
My Blog:

http://venkattechnicalblog.blogspot.com/
sagipiarius
sagipiarius
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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?
gdebacker
gdebacker
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 4
I'm having this exact same problem. Word for word with the query and the error.

Any help is appreciated.
CDA
CDA
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 523
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.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search