跨服务器查询数据库

--跨服务器查询
--1.执行一次 开启Ad Hoc Distributed Queries组件，在sql查询编辑器中执行如下语句：
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
go
--2.查询远程数据库，将查询结果复制到本地
select top 100 b.MoldId, a.moldNo, a.pic into myTemp from openrowset( 'SQLOLEDB ', '121.12.149.164\Tony,1045'; 'xinfowacXF'; 'New@Peak@2022048%',Xinfo_xinfe.dbo.mold_list) 
as a join (select m1.*,m2.MoldNo from MoldPicture m1 join Mold m2 on m1.MoldId=m2.Id ) b on a.moldno=b.MoldNo
where b.MoldNo='LG-16050'

--3.更新本地数据
select * from myTemp
select * from MoldPicture where MoldId='389028868968517'
update a set a.Picture=b.pic from MoldPicture a join myTemp b on a.MoldId=b.MoldId

--4.查询完成后，最终要 关闭Ad Hoc Distributed Queries组件，在sql查询编辑器中执行如下语句：
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure