Insert SQL statements into databases across databases and Domains

SQL inserts data INTO databases across databases and domains; SELECT * INTO [order] FROMOPENROWSET (SQLOLEDB, domain name; User Name; password, database name. if you run the command directly, the following error occurs: SQLServer blocks access to STATEMENTOpenRowsetOpenDatasource of the component AdHocDistributedQueries.

SQL inserts data INTO databases across databases and domains. SELECT * INTO [order] FROM OPENROWSET ('sqlodb', 'domain name'; 'username'; 'Password', database name. the following error is reported when the table name is directly executed. SQL Server blocks access to the STATEMENT 'openrowset/OpenDatasource 'of the 'ad Hoc Distributed Queries' component.

Insert SQL statements into databases across databases and domains;

SELECT * INTO [order] FROM
OPENROWSET
('Sqlodb', 'domain name'; 'username'; 'Password', database name. Table name)

The following error is reported during direct execution.

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 '. For more information about enabling 'ad Hoc Distributed querys', see "peripheral application configurator" in SQL Server books online ".

Solution

Exec sp_configure 'show advanced options', 1
Reconfigure
Exec sp_configure 'ad Hoc Distributed Queries ', 1
Reconfigure

SELECT * INTO [order] FROM
OPENROWSET
('Sqlodb', 'domain name'; 'username'; 'Password', database name. Table name)

After execution

In execution

Exec sp_configure 'ad Hoc Distributed Queries ', 0
Reconfigure
Exec sp_configure 'show advanced options', 0
Reconfigure

Cause of error:

When data is copied from a remote Server to a local machine, "SQL Server blocks access to the STATEMENT 'openrowset/OpenDatasource 'of the 'ad Hoc Distributed Queries' 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 '. An error occurred while enabling 'ad Hoc Distributed Queries '. Therefore, you can find the following solutions online:

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]

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

Verified by myself:

After querying the SQL books online, the official MSDN content is as follows:

By default, SQL Server does not allow distributed queries using OPENROWSET and OPENDATASOURCE. When this option is set to 1, SQL Server allows ad hoc access. When this option is set to 1, SQL Server allows ad hoc access. When this option is not set or is set to 0, SQL Server does not allow ad hoc access. When 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 remote data sources that use ole db. the OPENROWSET and OPENDATASOURCE functions are used for Ad hoc distributed queries to connect to remote data sources and use ole db. OPENROWSET and OPENDATASOURCE shocould be used only to reference ole db data sources that are accessed infrequently. OPENROWSET and OPENDATASOURCE should only be used to reference the ole db data source for infrequent access. For any data sources that will be accessed more than several times, define a linked server. will define the connection server For any data source accessed multiple times.

Security Note
Enabling the use of ad hoc names means that any authenticated login to SQL Server can access the provider. enable the project name, which means that any authentication is performed on a supplier that can be accessed by SQL Server. SQL Server administrators shocould enable this feature for providers that are safe to be accessed by any local login. the SQL Server administrator should enable this function as a supplier, which is secure and can be accessed by any local login. For more information, see the DisallowAdhocAccess option in Accessing External Data. For more information, see DisallowAdhocAccess options For access to External Data

MARK !!!