Solved

sp_configure ad hoc distributed queries in sql 2005

Posted on 2006-11-27
10
2,315 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

691 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