SQL Server blocks access to the STATEMENT of component & #39; Ad Hoc Distributed Queries & #39; & #39; OpenRowset/OpenDatasource & #39;, openrowset

SQL Server blocks access to the STATEMENT 'openrowset/OpenDatasource 'of the 'ad Hoc Distributed Queries' component. OpenRowset

Cause: It appears when the data is copied from the remote server to the local

"SQL Server has blocked 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 '. For more information about enabling 'ad Hoc Distributed Queries"

Error. Therefore, the following solutions are found during online search:

Enable Ad Hoc Distributed Queries:

[SQL]View plaincopyprint?

1. Exec sp_configure 'show advanced options', 1
2. Reconfigure
3. Exec sp_configure 'ad Hoc Distributed Queries ', 1
4. Reconfigure

After use, disable Ad Hoc Distributed Queries:

[SQL]View plaincopyprint?

1. Exec sp_configure 'ad Hoc Distributed Queries ', 0
2. Reconfigure
3. Exec sp_configure 'show advanced options', 0
4. Reconfigure

Verified by myself!

How to enable and disable Ad Hoc Distributed Queries

I found this error when I debugged the company program today.
SQL Server blocks Components
STATEMENT of 'ad Hoc Distributed querys' 'openrowset/OpenDatasource'
So I searched for a solution on the Internet.
Enable Ad Hoc Distributed Queries: execsp_configure 'show advanced options', 1reconfigureexecsp_configure 'ad Hoc Distributed Queries ', 1 reconfigure
Disable Ad Hoc Distributed Queries: execsp_configure 'ad Hoc Distributed Queries ', 0reconfigureexecsp_configure 'show advanced options', 0 reconfigure
Literally, This is a distributed query service. I checked the information on MSDN:
By default, SQL Server does not allow the use of OPENROWSET and OPENDATASOURCE for ad hoc distributed queries.
When this option is set to 1, SQL Server allows ad hoc access.
If this option is not set or set to 0, SQL Server does not allow ad hoc access.
Ad hoc distributed queries use the OPENROWSET and OPENDATASOURCE functions to connect to the remote data source using ole db.
OPENROWSET and OPENDATASOURCE should be used only when referencing infrequently accessed ole db data sources.
For data sources that will be frequently accessed, you should define a link to the server.
MSDN naturally gives the example code a lot of strength.
The following example enables ad hoc distributed queries and then uses OPENROWSET
The function queries the server with the name of Seattle1.
Sp_configure 'show advanced options', 1;
RECONFIGURE; sp_configure 'ad Hoc Distributed Queries ', 1;
RECONFIGURE; GOSELECTa. * FROMOPENROWSET ('sqlncl', 'server = Seattle1; Trusted_Connection = yes; ', 'select GroupName, Name, specified mentid
FROM AdventureWorks2012.HumanResources. Department
Order by GroupName, name') ASa; GO
The use of a temporary name means that any authenticated login name on SQL Server can access this interface.
The SQL Server administrator should enable this function for access interfaces that can be securely accessed by any local login name.

How does SQL Server enable Ad Hoc Distributed Queries?

1. enable Ad Hoc Distributed Queries. SQL Server blocks the access to the STATEMENT "" OpenRowset/OpenDatasource "of the component" "Ad Hoc Distributed Queries, this component has been closed as a project team of the server's Enron device. System Administrators can use the process.

Reconfigure; after the application is completed, remember to close it because it is an Enron hazard. Remember to execute the following SQL statement: exec sp_configure 'ad Hoc Distributed Queries ', 0 reconfigure exec sp_configure 'show advanced options', 0 reconfigure 2. Create a link to the application example server exec s p_add1_server "ITSV", "," "SQLOLEDB "", "" long-distance server name or IP address "" exec s p_add1_srvlogin "" ITSV "", "" false "", null, "" User Name "", "" password "" query example * ITSV. database Name. dbo. table Name import example * into Table ITSV. database Name. dbo. table names will no longer be used in the future to delete the linked server exec s p_dropserver "" ITSV "", "" droplogins "3. Connect to long distance/LAN data (openrowset/openquery/opendatasource) 1. openrowset query example * openrowset ("" SQLOLEDB "", "" SQL server name ""; "username"; "" password "", database name. dbo. table Name) generate local table * into Table openrowset ("" SQLOLEDB "", "" SQL server name ""; "username"; "" password "", database Name. dbo. table Name) import the local table to the long-distance table openrowset ("" SQLOLEDB "", "" SQL server name ""; "" username ";" "password "", database Name. dbo. table Name) * update the local table B set B. column A =. column A openrowset ("" SQLOLEDB & qu ...... remaining full text>