Solved

sp_configure ad hoc distributed queries in sql 2005

Posted on 2006-11-27
10
2,297 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

758 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

21 Experts available now in Live!

Get 1:1 Help Now