The configuration option 'Ad Hoc Distributed Queries' does not exist, or it may be an advanced option.
In order to enable Ad Hoc Distributed Queries you might possible run the below t-sql statement on your Microsoft SQL Server database instance.
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
If the output of the above sp_configure statement is as follows this sql exception is indicating that if you are not misspelling the 'Ad Hoc Distributed Queries' the "show advanced options" is not set for the SQL Server correctly to display configuration advanced settings.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'Ad Hoc Distributed Queries' does not exist, or it may be an advanced option.
To configure SQL Server 2005 or SQL Server 2008 is as easy as executing the sp_configure command with a different configuration option parameter.
EXEC sp_configure 'show advanced options', 1;
The output is as follows :
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Then run the "reconfigure" t-sql configuration command.
RECONFIGURE;
If you get the following sql exception from the sql engine :
Msg 5808, Level 16, State 1, Line 1
Ad hoc update to system catalogs is not supported.
Then execute the reconfigure command with "WITH OVERRIDE" option.
RECONFIGURE WITH OVERRIDE;
Now t-sql developers and SQL database administrators are ready to enable "Ad Hoc Distributed Queries" by using the following tsql sp_configure command.
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
The output of the sql sp_configure command should be as follows for a successfull execution.
Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.
The last step we should go through to enable Ad Hoc Distributed Queries on Microsoft SQL Server (2005/2008/2008 R2) is simply as running the reconfigure sql command.
RECONFIGURE
Now sql developers and SQL Server database administrators can create and execute Ad Hoc Distributed Queries without any problem on the installed SQL Server instance.
For problems with reconfigure sql command, you can check the sql article titled "reconfigure error : Ad hoc update to system catalogs is not supported.".
An additional sql tutorial is showing How to enable Ad Hoc Distributed Queries using sp_configure on SQL Server.
SQL developers can also find an other Ad Hoc Distributed Queries - sql OPENQUERY example at Running Active Directory Services Queries Using MS SQL Server T-SQL OPENQUERY Command.
|