How to enable Ad Hoc Distributed Queries

Question

When I run a query with OPENROWSET in SQL Server 2000 it works.

But the same query in SQL Server 2008 generates the following error:

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure

Answer 1

Try this command:

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

GO

EXEC sp_configure 'ad hoc distributed queries', 1

RECONFIGURE

GO