Solved

sp_configure ad hoc distributed queries in sql 2005

Posted on 2006-11-27
10
2,303 Views
Last Modified: 2010-08-05
Hi,
In my sql 2005 I have below configuration for ad hoc distributed queries

ad hoc distributed queries  0 1 0 0

is this mean it has been enable?
0
Comment
Question by:motioneye
  • 6
  • 4
10 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18021187
it is not configured
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18021197
the values are for
minimum          (0)
maximum         (1)
config value      (0)
and runvalue    (0)
0
 

Author Comment

by:motioneye
ID: 18021248
so how will the configure values look like?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18021271
that's what i shown in brackets
0
 

Author Comment

by:motioneye
ID: 18021287
yes I have the same values as what u show here maximum is 1 the rest is all 0, so it mean its already configure, am I right?
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18021662
in order to be configured , the 'config value' must be 1
for ur case it is '0' which means not configured
0
 

Author Comment

by:motioneye
ID: 18061403
but in sql 2005 BOL saying that 'config value'  = 0 means this option is enable, r u sure aneeshattingal

By default, SQL Server does not allow ad hoc distributed queries using OPENROWSET and OPENDATASOURCE against providers other than the SQL Native Client OLE DB Provider. When this option is set to 0, SQL Server allows ad hoc access against other providers. When this option is not set or is set to 1, SQL Server does not allow ad hoc access.
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 18062341
here is an example

Examples
A. List the advanced configuration options
This example shows how to set and list all configuration options. Advanced configuration options are displayed by first setting the show advanced option to 1. After this has been changed, executing sp_configure with no parameters displays all configuration options.

USE master
EXEC sp_configure 'show advanced option', '1'

--Here is the message:
Configuration option 'show advanced options' changed from 0 to 1.
Run the RECONFIGURE command to install.

RECONFIGURE
EXEC sp_configure
0
 

Author Comment

by:motioneye
ID: 18065300
Hi I'm confuse,
As an artcile from sql2005 BOL saying as below and u were saying differently, which one that I suppose to follow???


When this option is set to 0, SQL Server allows ad hoc access against other providers. When this option is not set or is set to 1, SQL Server does not allow ad hoc access.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18066969
>As an artcile from sql2005 BOL saying as below and u were saying differently, which one that I suppose to follow???

I haven't checked the article, the best thing you can do is to just do a testing with  values 0 and 1, this will give you an idea of which value is true
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now