SQL statements for cross-server data import

SQL statements for cross-server data import

 

Cross-server data import SQL statements and solutions cross-server data import SQL statements:

-- Customize the table name. Create the table SELECT * INTO TbName from openrowset ('sqloledb', '123' before import. 168.0.7 '; 'sa'; 'damon king', DBName. dbo. tbName)

-- Cross-Server Query example SELECT * from openrowset ('sqloledb', '2017. 168.0.7 '; 'sa'; damon king', DBName. dbo. tbName) Press F5. If 'ad Hoc Distributed Queries 'is not enabled, the following message is displayed:

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 ".

In this case, you can use sp_configure to enable 'ad Hoc Distributed Queries ':

-- Enable 'ad Hoc Distributed Queries'

EXEC SP_CONFIGURE 'show advanced options', 1 reconfigure exec SP_CONFIGURE 'ad Hoc Distributed Queries ', 1 RECONFIGURE

After the import is completed, it is best to disable 'ad Hoc Distributed Queries ': -- disable 'ad Hoc Distributed Queries'

 

EXEC SP_CONFIGURE 'ad Hoc Distributed Queries ', 0 reconfigure exec SP_CONFIGURE 'show advanced options', 0 RECONFIGURE

The cross-server data import task is completed!