Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sp_configure ad hoc distributed queries in sql 2005

Posted on 2006-11-27
10
Medium Priority
?
2,332 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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
 
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 2000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

604 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