Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2334
  • Last Modified:

sp_configure ad hoc distributed queries in sql 2005

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
motioneye
Asked:
motioneye
  • 6
  • 4
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
it is not configured
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
the values are for
minimum          (0)
maximum         (1)
config value      (0)
and runvalue    (0)
0
 
motioneyeAuthor Commented:
so how will the configure values look like?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Aneesh RetnakaranDatabase AdministratorCommented:
that's what i shown in brackets
0
 
motioneyeAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
in order to be configured , the 'config value' must be 1
for ur case it is '0' which means not configured
0
 
motioneyeAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
motioneyeAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
>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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now