远程复制表

• 第一种方法是右击数据库，点击Tasks下的import Data，根据向导完成操作。
• 第二种使用query，从远程数据库拷贝到当前数据库

select * into 目标表名 from openrowset( 'SQLOLEDB','远程Server'; '用户名';'密码',源表 )

 

• Msg 15281, Level 16, State 1, Line 1
  SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

      如果出现上述错误，那是SQL Server阻止了对‘Ad Hoc Distributed Queries’的访问，必须要先启用。而Ad Hoc Distributed Queries是Advanced Options，因此打开高级选项后才能对其进行设置。关闭时，也要先关Ad Hoc Distributed Queries，再关Adanced Options。

• 启用Ad Hoc Distributed Queries

exec sp_configure 'show advanced options',1 reconfigure exec sp_configure 'Ad Hoc Distributed Queries',1 reconfigure

• 关闭

exec sp_configure 'Ad Hoc Distributed Queries',0 reconfigure exec sp_configure 'show advanced options',0 reconfigure