SQL Server blocks the statement 'openrowset/opendataso of the 'ad hoc distributed queries 'component.

Today, Unit 1 ASP.. Net website, which has a function to export data. If any data is exported, an error is returned. The error message is: SQL Server blocks access to the statement 'openrowset/OpenDataSource 'of the 'ad hoc distributed queries' component because this component has been disabled as part of the server's security configuration. The system administrator can enable 'ad hoc distributed queries 'by using sp_configure '. Enable 'ad hoc
For more information about distributed queries, see "peripheral application configurator" in SQL Server books online ".

The error message is displayed because the SQL server's ad hoc distributed queries component is disabled. Here, the SQL Server version I use is 2005. You only need to enable ad hoc distributed queries, the method is as follows:

1. enable the ad hoc distributed queries component and run 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

2. Disable the ad hoc distributed queries component and run the following statement in the SQL query Editor:

Exec sp_configure 'ad hoc distributed queries ', 0
Reconfigure
Exec sp_configure 'show advanced options', 0
Reconfigure