SQLServer访问远程信息库--采取openrowset/opendatasource的方式

一、使用openrowset/opendatasource第一个启用Ad Hoc Distributed Queries因为这项服务不安全。SqlServer默认设置为禁用。
SQL Server 该组件已被阻止。 Ad Hoc Distributed Queries 的 STATEMENTOpenRowset/OpenDatasource的访问，
因为此组件已作为此服务器安全配置的一部分关闭。系统管理员可以使用sp_configure 启用 Ad Hoc Distributed Queries。

1、启用Ad Hoc Distributed Queries服务方法，对其执行以下查询语句：
exec sp_configure show advanced options,1
reconfigure
exec sp_configure Ad Hoc Distributed Queries,1
reconfigure

2使用后，记得关闭，因为这是一个潜在的安全隐患。,请记住执行以下命令SQL语句：
exec sp_configure Ad Hoc Distributed Queries,0
reconfigure
exec sp_configure show advanced options,0
reconfigure

2.使用示例

-->--创建链接服务器 
exec sp_addlinkedserver   ITSV ,  , SQLOLEDB , 远程服务器名称或ip地址  
exec sp_addlinkedsrvlogin ITSV , false ,null, 用户名 , 密码  

--查询示例 
select * from ITSV.数据库名.dbo.表名 

--导入示例 
select * into 表 from ITSV.数据库名.dbo.表名 

--以后不再使用链接服务器时，请将其删除。 
exec sp_dropserver  ITSV , droplogins  

--连接远程/局域网数据(openrowset/openquery/opendatasource) 
--1、openrowset 

--查询示例 
select * from openrowset( SQLOLEDB , sql服务器名 ; 用户名 ; 密码 ,数据库名.dbo.表名) 

--生成本地表 
select * into 表 from openrowset( SQLOLEDB , sql服务器名 ; 用户名 ; 密码 ,数据库名.dbo.表名) 

--将本地表导入远程表 
insert openrowset( SQLOLEDB , sql服务器名 ; 用户名 ; 密码 ,数据库名.dbo.表名) 
select *from 本地表 

--更新本地表 
update b 
set b.列A=a.列A 
from openrowset( SQLOLEDB , sql服务器名 ; 用户名 ; 密码 ,数据库名.dbo.表名)as a inner join 本地表 b 
on a.column1=b.column1 

--openquery使用需要创建连接。 

--首先创建连接以创建链接服务器。 
exec sp_addlinkedserver   ITSV ,  , SQLOLEDB , 远程服务器名称或ip地址  
--查询 
select * 
FROM openquery(ITSV,  SELECT *  FROM 数据库.dbo.表名 ) 
--将本地表导入远程表 
insert openquery(ITSV,  SELECT *  FROM 数据库.dbo.表名 ) 
select * from 本地表 
--更新本地表 
update b 
set b.列B=a.列B 
FROM openquery(ITSV,  SELECT * FROM 数据库.dbo.表名 ) as a  
inner join 本地表 b on a.列A=b.列A 

--3、opendatasource/openrowset 
SELECT   * 
FROM   opendatasource( SQLOLEDB ,  Data Source=ip/ServerName;User ID=登陆名;Password=密码  ).test.dbo.roy_ta 
--将本地表导入远程表 
insert opendatasource( SQLOLEDB ,  Data Source=ip/ServerName;User ID=登陆名;Password=密码 ).数据库.dbo.表名 
select * from 本地表

3.我自己写的例子

--openrowset使用OLEDB一些例子
select * from openrowset(SQLOLEDB,Server=(local);PWD=***;UID=sa;,select * from TB.dbo.school) as t
select * from openrowset(SQLOLEDB,Server=(local);PWD=***;UID=sa;,TB.dbo.school) as t
select * from openrowset(SQLOLEDB,Server=(local);Trusted_Connection=yes;,TB.dbo.school) as t
select * from openrowset(SQLOLEDB,(local);sa;***,select * from TB.dbo.school) as t
select * from openrowset(SQLOLEDB,(local);sa;***,TB.dbo.school) as t
select * from openrowset(SQLOLEDB,(local);sa;***,select school.id as id1,people.id as id2 from TB.dbo.school inner join TB.dbo.people on school.id=people.id) as t

--openrowset使用SQLNCLI一些例子(SQLNCLI在SqlServer2005以上内容仅适用于)
select * from openrowset(SQLNCLI,(local);sa;***,select * from TB.dbo.school) as t
select * from openrowset(SQLNCLI,Server=(local);Trusted_Connection=yes;,select * from TB.dbo.school) as t
select * from openrowset(SQLNCLI,Server=(local);UID=sa;PWD=***;,select * from TB.dbo.school) as t
select * from openrowset(SQLNCLI,Server=(local);UID=sa;PWD=***;,TB.dbo.school) as t
select * from openrowset(SQLNCLI,Server=(local);UID=sa;PWD=***;DataBase=TB,select * from dbo.school) as t

--openrowset其他使用
insert openrowset(SQLNCLI,Server=(local);Trusted_Connection=yes;,select name from TB.dbo.school where id=1) values(ghjkl)/*要不要where尽管如此，插入一行*/
update openrowset(SQLNCLI,Server=(local);Trusted_Connection=yes;,select name from TB.dbo.school where id=1) set name=kkkkkk
delete from openrowset(SQLNCLI,Server=(local);Trusted_Connection=yes;,select name from TB.dbo.school where id=1)

--opendatasource使用SQLNCLI一些例子
select * from opendatasource(SQLNCLI,Server=(local);UID=sa;PWD=***;).TB.dbo.school as t
select * from opendatasource(SQLNCLI,Server=(local);UID=sa;PWD=***;DataBase=TB).TB.dbo.school as t

--opendatasource使用OLEDB的例子
select * from opendatasource(SQLOLEDB,Server=(local);Trusted_Connection=yes;).TB.dbo.school as t

--opendatasource其他使用
insert opendatasource(SQLNCLI,Server=(local);Trusted_Connection=yes;).TB.dbo.school(name) values(ghjkl)/*要不要where尽管如此，插入一行*/
update opendatasource(SQLNCLI,Server=(local);Trusted_Connection=yes;).TB.dbo.school set name=kkkkkk
delete from opendatasource(SQLNCLI,Server=(local);Trusted_Connection=yes;).TB.dbo.school where id=1

--openquery使用OLEDB一些例子
exec sp_addlinkedserver   ITSV, , SQLOLEDB,(local) 
exec sp_addlinkedsrvlogin ITSV, false,null, sa, ***
select * FROM openquery(ITSV,  SELECT *  FROM TB.dbo.school ) 

--openquery使用SQLNCLI一些例子
exec sp_addlinkedserver   ITSVA, , SQLNCLI,(local) 
exec sp_addlinkedsrvlogin ITSVA, false,null, sa, ***
select * FROM openquery(ITSVA,  SELECT *  FROM TB.dbo.school ) 

--openquery其他使用
insert openquery(ITSVA,select name from TB.dbo.school where id=1) values(ghjkl)/*要不要where尽管如此，插入一行*/
update openquery(ITSVA,select name from TB.dbo.school where id=1) set name=kkkkkk
delete openquery(ITSVA,select name from TB.dbo.school where id=1)

四、总结

可以看到SqlServer有多种方法可以连接多台服务器。3种

其中，我个人认为openrowset优选地，它使用简单，并且支持在连接时制定查询语句的灵活性。

openquery指定查询语句可以在查询时灵活使用也很好，但应该在查询之前使用。exec sp_addlinkedserver和exec sp_addlinkedsrvlogin建立服务器和服务器连接有点麻烦。

opendatasource更糟糕的是，他不能在连接时指定查询。它用起来有点笨拙。

也可以连接到遥控器Analysis服务器做MDX查询、重用T-Sql嵌套查询是否可见T-SQL远程查询功能非常强大。

在T-SQL在在语句中访问远程数据库。。(openrowset/opendatasource/openquery) - PowerCoder - 博客园