创建链接服务器
1、创建方法
--exec sp_addlinkedserver '注册名','','SQLOLEDB','远程服务器名或ip地址和端口（ip和端口中间用逗号）'
--exec sp_addlinkedsrvlogin '注册名','false',null,'用户名','密码' exec sp_dropserver '注册名', 'droplogins'
--go
2、具体说明
sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]
[ , [ @provider= ] 'provider_name' ]
[ , [ @datasrc= ] 'data_source' ]
[ , [ @location= ] 'location' ]
[ , [ @provstr= ] 'provider_string' ]
[ , [ @catalog= ] 'catalog' ]
参数
[ @server = ] 'server'
要创建的链接服务器的名称。server 的数据类型为 sysname，没有默认值。
[ @srvproduct = ] 'product_name'
要添加为链接服务器的 OLE DB 数据源的产品名称。product_name 的数据类型为 nvarchar(128)，默认值为 NULL。如果为 SQL Server，则不必指定 provider_name、data_source、location、provider_string 和 catalog。
[ @provider = ] 'provider_name'
与此数据源对应的 OLE DB 访问接口的唯一编程标识符 (PROGID)。对于当前计算机中安装的指定 OLE DB 访问接口，provider_name 必须唯一。provider_name 的数据类型为 nvarchar(128)，默认值为 NULL；但如果忽略 provider_name，则使用 SQLNCLI。SQLNCLI 是 SQL 本机 OLE DB 访问接口。OLE DB 访问接口应以指定的 PROGID 在注册表中注册。
[ @datasrc = ] 'data_source'
由 OLE DB 访问接口解释的数据源的名称。data_source 的数据类型为 nvarchar(4000)。data_source 作为 DBPROP_INIT_DATASOURCE 属性传递以初始化 OLE DB 访问接口。
[ @location = ] 'location'
由 OLE DB 访问接口解释的数据库的位置。location 的数据类型为 nvarchar(4000)，默认值为 NULL。location 作为 DBPROP_INIT_LOCATION 属性传递以初始化 OLE DB 访问接口。
[ @provstr = ] 'provider_string'
OLE DB 访问接口特定的连接字符串，它可标识唯一的数据源。provider_string 的数据类型为 nvarchar(4000)，默认值为 NULL。provstr 或传递给 IDataInitialize 或设置为 DBPROP_INIT_PROVIDERSTRING 属性以初始化 OLE DB 访问接口。
在针对 SQL 本机客户端 OLE DB 访问接口创建链接服务器后，可将 SERVER 关键字用作 SERVER=servername/instancename 来指定实例，以指定特定的 SQL Server 实例。servername 是运行 SQL Server 的计算机名称，instancename 是用户将连接到的特定 SQL Server 实例的名称。
[ @catalog = ] 'catalog'
与 OLE DB 访问接口建立连接时所使用的目录。catalog 的数据类型为 sysname，默认值为 NULL。catalog 作为 DBPROP_INIT_CATALOG 属性传递以初始化 OLE DB 访问接口。在针对 SQL Server 实例定义链接服务器时，目录指向链接服务器映射到的默认数据库。
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
[ , [ @useself = ] 'TRUE' | 'FALSE' | NULL ]
[ , [ @locallogin = ] 'locallogin' ]
[ , [ @rmtuser = ] 'rmtuser' ]
[ , [ @rmtpassword = ] 'rmtpassword' ]参数
[@rmtsrvname =] 'rmtsrvname'
是应用登录映射的链接服务器名称。rmtsrvname 的数据类型为 sysname，没有默认设置。（对应sp_addlinkedserver 配置的server）。
[@useself =] 'useself'
决定用于连接到远程服务器的登录名称。useself 的数据类型为 varchar(8)，默认设置为 TRUE。true 值指定 SQL Server 验证的登录使用其自己的凭据以连接到 rmtsrvname，而忽略 rmtuser 和 rmtpassword 参数。false 值指定 rmtuser 和 rmtpassword 参数用来连接到特定 locallogin 的 rmtsrvname。如果 rmtuser 和 rmtpassword 也设置为 NULL，则连接链接服务器不需使用任何登录或密码。对于 Windows NT 身份验证登录而言，useself 为 true 则无效，除非 Microsoft Windows NT® 环境支持安全帐户委托，并且，提供程序支持 Windows 身份验证（此时不再需要创建值为 true 的映射，不过创建仍然有效）。
[ @locallogin =] 'locallogin'
本地服务器上的登录。locallogin 的数据类型为 sysname，默认设置为 NULL。NULL 值指定此条目将应用于所有连接到 rmtsrvname 的本地登录。如果值不为 NULL，则 locallogin 可以是一个 SQL Server 登录或 Windows NT 用户。必须授予 Windows NT 用户直接访问 SQL Server 或通过其作为已授予访问权限的组的成员来访问 SQL Server 的权限。
[@rmtuser =] 'rmtuser'
当 useself 为 false 时，用来连接 rmtsrvname 的用户名，rmtuser 的数据类型为 sysname，默认设置为 NULL。
[@rmtpassword =] 'rmtpassword'
与 rmtuser 相关的密码。rmtpassword 的数据类型为 sysname，默认设置为 NULL。
3.实例展示
--创建链接服务器
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
>exec sp_addlinkedserver @server=’别名’,@provider=’sqloledb’,@srvproduct=’’,@datasrc=’远程服务器IP’
4、不同数据库的创建
/SQL SERVER
Exec sp_droplinkedsrvlogin DBVIP,Null
Exec sp_dropserver DBVIP
EXEC sp_addlinkedserver
@server='DBVIP',--被访问的服务器别名
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='Server2' --要访问的服务器
EXEC sp_addlinkedsrvlogin
'DBVIP', --被访问的服务器别名
'false',
NULL,
'sa', --帐号
'thankyoubobby' --密码
Select * from DBVIP.pubs.dbo.orders
/ORACLE
需要SQL SERVER服务器中安装ORACLE客户端；
Exec sp_droplinkedsrvlogin demo,Null
Exec sp_dropserver demo
go
EXEC sp_addlinkedserver
@server ='demo', --要创建的链接服务器别名
@srvproduct='Oracle', --产品名称
@provider='MSDAORA', --OLE DB 字符
@datasrc='ServiceName' --数据源 oracle"ora92"network"admin"tnsnames.ora 查看
EXEC sp_addlinkedsrvlogin
'demo', --已建立的链接服务器名
'false', -- 固定 */
NULL, --为每个登陆SQL SERVER的用户使用此链接服务器，否则写用户名 */
'userid', --帐号
'password' –密码
go
SELECT * FROM demo.. USERID.TABLE
--注意用大写，因为在Oracle的数据字典中为大写
数据库的格式必须是链接服务器名..ORACLE用户名.表名.
SELECT * FROM OPENQUERY(demo ,'select * from tbdemo' )
UPDATE OPENQUERY (demo, 'SELECT id FROM tbdemo WHERE id = 101')
SET name = 'hello';
INSERT OPENQUERY (demo, 'SELECT id FROM tbdemo')
VALUES ('hello');
DELETE OPENQUERY (demo, 'SELECT id FROM tbdemo WHERE name = ''hello''');
如此则在SQL SERVER中就可以访问ORACLE的数据了。
建立的链接服务器可以在企业管理器中看见.
/ACCESS
--建立连接服务器
EXEC sp_addlinkedserver
'ai', --要创建的链接服务器名称
‘access', --产品名称
'Microsoft.Jet.OLEDB.4.0', --OLE DB 字符
'd:"testdb"db.mdb' --数据源 -- 盘符:"路径"文件名 -- ""网络名"共享名"文件名 (网络版本)
GO
--创建链接服务器上远程登录之间的映射
--链接服务器默认设置为用登陆的上下文进行
--现在我们修改为连接链接服务器不需使用任何登录或密码
exec sp_addlinkedsrvlogin
'ai',
'false'
go
select * from ai...mytable
/EXCEL
--建立连接服务器
EXEC sp_addlinkedserver
'ai_ex', --要创建的链接服务器名称
'ex', --产品名称
'Microsoft.Jet.OLEDB.4.0', --OLE DB 字符
'd:"testdb"mybook.xls' , --数据源 -- 盘符:"路径"文件名-- ""网络名"共享名"文件名 (网络版本)
null,
'Excel 5.0' --OLE DB 提供程序特定的连接字符串
GO----创建链接服务器上远程登录之间的映射
--链接服务器默认设置为用登陆的上下文进行
--现在我们修改为连接链接服务器不需使用任何登录或密码
exec sp_addlinkedsrvlogin
'ai_ex',
'false'
go--查询数据
select * from ai_ex...sheet3$
/MS SQL
--建立连接服务器
EXEC sp_addlinkedserver
'ai_mssql', --要创建的链接服务器名称
'ms', --产品名称
'SQLOLEDB', --OLE DB 字符
'218.204.111.111,3342' --数据源
GO--创建链接服务器上远程登录之间的映射
EXEC sp_addlinkedsrvlogin
'ai_mssql',
'false',
NULL,
'zhangzhe', --远程服务器的登陆用户名
'fish2231' --远程服务器的登陆密码
go--查询数据
select * from ai_mssql.pubs.dbo.jobs
--还有一个更简单的办法
--这种方式在链接服务器建立后，它是默认开放RPC的
--建立连接服务器
EXEC sp_addlinkedserver
'218.204.111.111,3342', --要创建的链接服务器名称
'SQL Server' --这里就用数据源作名称
GO--创建链接服务器上远程登录之间的映射
EXEC sp_addlinkedsrvlogin
'218.204.111.111,3342',
'false',
NULL,
'zhangzhe', --远程服务器的登陆用户名
'fish2231' --远程服务器的登陆密码
go--查询数据
select * from [218.204.253.131,3342].pubs.dbo.jobs
go
5、手动配置
一、打开服务器对象-》链接服务器：（如下图）
二、选择“链接服务器”，右键，“新建链接服务器”，弹出“新建联机服务”窗口：
（1）在“常规”选项卡中，输入“链接服务器名称”为“GPS”（可以任意写）,，“服务器类型”选择“其他数据源”中的，访问接口为“SQL Native Client”；产品名称为“Ms SQL”；数据源为“222.35.45.123,2433”（IP地址+逗号+端口号）。如下图：
（2）在“安全性”选项卡中，选择“使用此安全上下文建立连接”，
在“远程登录”输入“GPS_new”（即用户名），在“使用的密码”输入“密码”（即对应的密码）。如下图：
（3）在“服务器选项”选项卡中，设置“RPC”和“RPC Out”都为“true”。如下图：
三、点击“确定”。即成功的建立“链接服务器”。
可以使用sql语句进行测试：
语法： select * from [链接服务器名].[远程数据库名].[所有者].[表名]
例如： select * from GPS.GPS_database.dbo.Carinfo_FK
----------------------------------------------------------------------------------------
