Enable “Ad Hoc Distributed queries” by sp_configure in SQL-Server 2005

If you want to use OpenDataSource or OpenRowSet query than you must have to enable “Ad Hoc Distributed queries”. It is disabled by default as a part of security configuration as per Microsoft.
You can set this option by two ways.
1.) sp_configure command
2.) Surface area configuration.

Since I am a script bee, I will explain steps for sp_configure.
You can run sp_configure to see current status of “run_value” field. If you don’t able to see “Ad Hoc Distributed queries” in the results set. You have to enable “Show advanced option” by following command.
sp_configure ‘show advanced options’, 1
reconfigure
GO
You may get message
Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.


Once you are done with that, you can see “Ad Hoc Distributed queries” in list of sp_configure. You have to set “Run_value” to “1”. If it is “0” than run following command.

sp_configure ‘Ad Hoc Distributed Queries’, 1
reconfigure
GO
Which will show you following message.
Configuration option ‘Ad Hoc Distributed Queries’ changed from 0 to 1. Run the RECONFIGURE statement to install.
Once you will go through the procedure described here, you will be able to run OpenRowSet and OpenDataSource query.

Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

One thought on “Enable “Ad Hoc Distributed queries” by sp_configure in SQL-Server 2005

  1. Pingback: Populate table from Stored Procedure resultset in SQL Server

Comments are closed.