How to Enable Ad Hoc Distributed Queries in SQL Server?

Enabling Ad Hoc Distributed Queries in SQL Server

OpenRowset queries that functioned seamlessly in SQL Server 2000 often encounter the following error in SQL Server 2008:

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.

To resolve this error and enable Ad Hoc Distributed Queries, follow these steps using sp_configure:

-- Show advanced server configuration options
EXEC sp_configure 'show advanced options', 1
RECONFIGURE

-- Enable Ad Hoc Distributed Queries
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE

After executing these commands, Ad Hoc Distributed Queries should be enabled for your SQL Server 2008 instance, allowing OpenRowset queries to run successfully.

The above is the detailed content of How to Enable Ad Hoc Distributed Queries in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!