![SQL Server 跨库查询 SQL Server 跨库查询](https://image.shishitao.com:8440/aHR0cHM6Ly9ia3FzaW1nLmlrYWZhbi5jb20vdXBsb2FkL2NoYXRncHQtcy5wbmc%2FIQ%3D%3D.png?!?w=700&webp=1)
1. 开启Ad Hoc Distributed Queries组件,在sql查询编辑器中执行如下语句:
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
2. 跨库查询操作
select * from OPENDATASOURCE(
'SQLOLEDB',
'Data Source=远程ip;User ID=sa;Password=密码'
).库名.dbo.表名
例如:
select * from OPENDATASOURCE(
'SQLOLEDB',
'Data Source=10.2.2.53;User ID=sa;Password=779i$4'
).MDXT_O_S_01.dbo.MDMZGZDRecord
3. 关闭Ad Hoc Distributed Queries组件,在sql查询编辑器中执行如下语句:
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure