SQL Server 访问远程数据库(openrowset/opendatasource/openquery)

1）启用Ad Hoc Distributed Queries

     在使用openrowset/opendatasource前首先要启用Ad Hoc Distributed Queries 服务，SQL Server阻止了该组件对STAMENT'openrowset/opendatasource'的访问，因为这个服务不安全，所以Sql server默认是关闭的。     

exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
--使用完毕后，记得一定要关闭它，因为这是一个安全隐患，切记执行下面的sql语句
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

2）openrowset

--查询
select * from openrowset('SQLOLEDB','sql 服务器名','用户名','密码',数据库名.dbo.表名)
--生成本地表
select * into #tmp from openrowset('SQLOLEDB','sql 服务器名','用户名','密码',数据库.dbo.表名)
--把本地表导入远程表
insert openrowset('SQLOLEDB','sql 服务器名','用户名','密码',数据库名.dbo.表名)
select * from 本地表

3）opendatasource

--查询
select * from opendatasource('SQLOLEDB','Data Source=ip/ServerName;User ID=登陆名;Password=密码').数据库.dbo.表名
--把本地表导入远程表
insert opendatasource('SQLOLEDB','Data Source=ip/ServerName;User ID=登陆名;Password=密码').数据库.dbo.表名
select * from 本地表

4）openquery

--Linked Server链接服务器('ITSV'是链接服务器名，自定义)
--使用SQLOLEDB
exec sp_addlinkedserver 'ITSV','','SQLOLEDB','远程服务器名或ip地址'
--使用SQLNCLI
exec sp_addlinkedserver 'ITSV','','SQLNCLI','远程服务器名或ip地址'
exec sp_addlinkedsrvlogin 'ITSV','false',null,'用户名','密码'
--查询
select * from ITSV.数据库名.dbo.表名
--删除链接服务器
exec sp_dropserver 'ITSV','droplogins'

--openquery用法
--查询
select * from openquery('ITSV','select * from 数据库名.dbo.表名')
--把本地表导入远程表
insert openquery('ITSV','select * from 数据库名.dbo.表名')
select * from 本地表

     访问远程数据库(openrowset/opendatasource/openquery)的表时，可以对其增、删、改、查等操作。