Wednesday, May 20, 2009

[SQLServer2005] Enable Ad hoc distributed queries

The Ad Hoc Distrubuted Queries advanced feature is disabled by default - it needs to be enabled if you wanna run OpenRowSet or OpenDataSource operations (queries on excel spreadsheets and other stuff). 

To enable it run the following script and you should be OK:

-- allows you to see advaced options status
sp_configure 'show advanced options', 1
-- enables Ad Hoc Distributed Queries
sp_configure 'Ad Hoc Distributed Queries', 1
-- shows you the full list of features

The same result can be achieved by opening SQL Server Area Configuration tool for features and after picking Ad Hoc Distributed Queries ticking the "Enable ..." checkbox.

That'd be all.

No comments: