Solved

sp_configure ad hoc distributed queries in sql 2005

Posted on 2006-11-27
10
2,307 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Sum totals 2 25
Help Required 3 96
Unable to Uninstall Visual Studio 2015 7 26
New to SSRS, extremely slow running report. 8 20
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

772 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