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

Use today

OpenDataSource ('sqloledb', 'Data source = server; user id = user; Password = 111111 '). testdb. DBO. Table

Cross-Database Query

The error message is: SQL Server blocks access to the statement 'openrowset/OpenDataSource 'of the 'ad hoc distributed querys' component, this component is disabled as part of the server security configuration. The system administrator can enable 'ad hoc distributed queries 'by using sp_configure '. For more information about enabling 'ad hoc distributed querys', 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