Workaround for SQL Server blocking access to component ' Ad Hoc distributed Queries ' STATEMENT ' Openrowset/opendatasource '

Error: SQL Server blocked access to STATEMENT ' openrowset/opendatasource ' of component ' Ad Hoc distributed Queries ' because this component has been shut down as part of this server's security configuration Closed. System administrators can enable ' Ad Hoc distributed Queries ' by using sp_configure. For more information about enabling ' Ad Hoc distributed Queries ', see "surface area Configurator" in SQL Server Books Online.

The cause of the problem: look at the error prompt and know because SQL Server's ad HOC distributed Queries component is disabled (the SQL Server version I used here is 2005)

Solution: only need to open ad Hoc distributed queries on it, the specific steps are as follows:

Open the ad HOC distributed queries component and execute the following statement in the SQL Query Editor:

exec sp_configure ' show advanced options ', 1
Reconfigure
exec sp_configure ' Ad Hoc distributed Queries ', 1
Reconfigure

Add: Close the ad Hoc distributed queries component and execute the following statement in SQL Query Editor

exec sp_configure ' Ad Hoc distributed Queries ', 0
Reconfigure
exec sp_configure ' show advanced options ', 0
Reconfigure

Workaround for SQL Server blocking access to component ' Ad Hoc distributed Queries ' STATEMENT ' Openrowset/opendatasource '