–第一部分(要点)
–永久访问方式（需对访问远程数据库进行经常性操作）时设置链接数据库
Exec sp_addlinkedserver ‘MyLinkServer‘,‘‘,‘SQLOLEDB‘,‘远程服务器名或ip地址‘
–或：
Exec sp_addlinkedserver ‘远程服务器‘,N‘SQL Server‘
Exec sp_addlinkedsrvlogin ‘MyLinkServer‘,‘false‘,null,‘用户名‘,‘密码‘
–返回本地服务器中定义的链接服务器列表
Exec sp_linkedservers
–返回表的字段中，其中对MSSQL而言：SRV_NAME(链接服务器的名称)，SRV_DATASOURCE(与指定链接服务器对应的服务器名)
–访问表时以四部分名称形式
–查询示例
Select * From MyLinkServer.数据库名.dbo.表名
–导入示例
Select * Into 表 From MyLinkServer.数据库名.dbo.表名
–以后不再使用时删除链接服务器
Exec sp_dropserver ‘MyLinkServer‘,‘droplogins‘ –以用户名登录时
–或：
Exec sp_dropserver ‘MyLinkServer‘ –未以用户名登录时
–连接远程/局域网数据(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 ‘MyLinkServer‘,‘‘,‘SQLOLEDB‘,‘远程服务器名或IP‘
–查询
Select * From OpenQuery(MyLinkServer,‘Select * From 数据库.dbo.表名‘)
–把本地表导入远程表
Insert OpenQuery(MyLinkServer,‘Select * From 数据库.dbo.表名‘)
Select * From 本地表
–更新本地表
Update b
Set b.列B=a.列B
From OpenQuery(MyLinkServer,‘Select * From 数据库.dbo.表名‘) As a
Inner Join 本地表 b On a.列A=b.列A
–3、OpenDataSource
Select * From OpenDataSource(‘SQLOLEDB‘,‘Data Source=远程服务器名或IP;User ID=登陆名;Password=密码‘).test.dbo.roy_ta
–把本地表导入远程表
Insert OpenDataSource(‘SQLOLEDB‘,‘Data Source=远程服务器名或IP;User ID=登陆名;Password=密码‘).数据库.dbo.表名
Select * From 本地表
–第二部分(实际示例)
–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 ‘MyLinkServer‘,‘‘,‘SQLOLEDB‘,‘(local)‘
Exec sp_addlinkedsrvlogin ‘MyLinkServer‘,‘false‘,null,‘sa‘,‘***‘
Select * From OpenQuery(MyLinkServer, ‘Select * From TB.dbo.school‘)
–OpenQuery使用SQLNCLI的一些例子
Exec sp_addlinkedserver ‘MyLinkServerA‘,‘‘,‘SQLNCLI‘,‘(local)‘
Exec sp_addlinkedsrvlogin ‘MyLinkServerA‘,‘false‘,null,‘sa‘,‘***‘
Select * From OpenQuery(MyLinkServerA,‘Select * From TB.dbo.school‘)
–OpenQuery其他使用
Insert OpenQuery(MyLinkServerA,‘Select name From TB.dbo.school Where id=1‘) Values(‘ghjkl‘) /*要不要Where都一样，插入一行*/
Update OpenQuery(MyLinkServerA,‘Select name From TB.dbo.school Where id=1‘) Set name=‘kkkkkk‘
Delete OpenQuery(MyLinkServerA,‘Select name From TB.dbo.school Where id=1‘)