SQL Server Surface area configuration is deprecated in SQL Server 2008, so this is how you should enable adhoc queries by setting OPENROWSET and OPENDATASOURCE options.

Run the following commands, one at a time:

sp_configure “show advanced options”,1

Output: Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

reconfigure

Output: Command(s) completed successfully.

sp_configure “Ad Hoc Distributed Queries”,1

Output: Configuration option 'Ad Hoc Distributed Queries' changed from 0 to 1. Run the RECONFIGURE statement to install.

reconfigure

Output: Command(s) completed successfully.

And that is it.

Advertisements