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.