Microsoft SQL Server跨服务器查询

创建链接服务器

sp_addlinkedserver

创建链接服务器。 链接服务器让用户可以对 OLE DB 数据源进行分布式异类查询。 使用 sp_addlinkedserver 创建链接服务器后，可对该服务器运行分布式查询。 如果链接服务器定义为 SQL Server实例，则可执行远程存储过程。

语法

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]   
     [ , [ @provider= ] 'provider_name' ]  
     [ , [ @datasrc= ] 'data_source' ]   
     [ , [ @location= ] 'location' ]   
     [ , [ @provstr= ] 'provider_string' ]   
     [ , [ @catalog= ] 'catalog' ]

参数

[ @server =] ‘ 服务器 ‘
要创建的链接服务器的名称。 server 的数据类型为 sysname，无默认值。

[ @srvproduct =] ‘ product_name ‘
要添加为链接服务器的 OLE DB 数据源的产品名称。 product_name 为 nvarchar ( 128 )，默认值为 NULL。 如果不需要指定 SQL Server、 provider_name、 data_source、 位置、 provider_string 和 目录 。

[ @provider =] ‘ provider_name ‘
与此数据源对应的 OLE DB 访问接口的唯一编程标识符 (PROGID)。 对于当前计算机上安装的指定 OLE DB 提供程序， provider_name 必须是唯一的。 provider_name 为 nvarchar (128)，默认值为 NULL;但是，如果省略 provider_name ，则使用 sqlncli.msi。

使用 SQLNCLI.MSI 将重定向 SQL Server 到 SQL Server Native Client OLE DB 提供程序的最新版本。 OLE DB 提供程序应以指定的 PROGID 在注册表中注册。

以前的 Microsoft OLE DB Provider for SQL Server (SQLOLEDB) 和 SQL Server Native Client OLEDB 提供程序 (SQLNCLI) 仍然不推荐使用，不建议在新的开发工作中使用它们。 相反，请使用新的 Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL)，其将使用最新的服务器功能进行更新。

[ @datasrc =] ‘ data_source ‘
由 OLE DB 访问接口解释的数据源的名称。 data_source (4000 ) 为 nvarchar。 data_source 作为 DBPROP_INIT_DATASOURCE 属性传递以初始化 OLE DB 提供程序。

[ @location =] ‘ 位置 ‘
由 OLE DB 访问接口解释的数据库的位置。 location (4000 ) 为 NVARCHAR，默认值为 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 Server Native Client OLE DB 提供程序创建链接服务器时，可以通过使用 server 关键字 as server =servername \ instancename 指定的特定实例来指定实例 SQL Server 。 servername 是运行的计算机的名称 SQL Server ， instancename 是 SQL Server 用户将连接到的特定实例的名称。

若要访问镜像数据库，则连接字符串必须包含数据库名称。 该名称是数据访问接口启用故障转移尝试所必需的。 可以在 @ provstr 或 @ catalog 参数中指定数据库。 此外，连接字符串还可以提供故障转移伙伴名称。

返回代码值

0（成功）或 1（失败）

sp_addlinkedsrvlogin

创建或更新 SQL Server 本地实例上的登录名与远程服务器中安全帐户之间的映射。

语法

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'   
     [ , [ @useself = ] { 'TRUE' | 'FALSE' | NULL } ]   
     [ , [ @locallogin = ] 'locallogin' ]   
     [ , [ @rmtuser = ] 'rmtuser' ]   
     [ , [ @rmtpassword = ] 'rmtpassword' ]

参数

[ @rmtsrvname = ] 'rmtsrvname'
应用登录映射的链接服务器的名称。 rmtsrvname 的值为 sysname，无默认值。

[ @useself = ] { 'TRUE' | 'FALSE' | NULL }'
确定是通过模拟本地登录名连接到 rmtsrvname ，还是显式提交登录名和密码。 数据类型为 varchar ( 8 )，默认值为 TRUE。

如果值为 TRUE，则指定登录名使用其自己的凭据连接到 rmtsrvname，并忽略 rmtuser 和 rmtpassword 参数。 FALSE 指定 rmtuser 和 rmtpassword 参数用于连接到指定 locallogin 的 rmtsrvname 。 如果将 rmtuser 和 RMTPASSWORD 设置为 NULL，则不会使用登录名或密码连接到链接服务器。

[ @locallogin = ] 'locallogin'
本地服务器上的登录。 locallogin 的值为 sysname，默认值为 NULL。 NULL 指定此条目适用于连接到 rmtsrvname 的所有本地登录名。 如果不为 NULL，则 locallogin 可以为 SQL Server 登录名或 Windows 登录名。 对于 Windows 登录来说，必须以直接的方式或通过已被授权访问的 Windows 组成员身份授予其访问 SQL Server 的权限。

[ @rmtuser = ] 'rmtuser'
当为 FALSE 时，用于连接到 rmtsrvname 的远程登录名 @useself 。 当远程服务器是 SQL Server 不使用 Windows 身份验证的实例时， rmtuser 是一个 SQL Server 登录名。 rmtuser 的值为 sysname，默认值为 NULL。

[ @rmtpassword = ] 'rmtpassword'
与 rmtuser 关联的密码。 rmtpassword 的值为 sysname，默认值为 NULL。

返回代码值

0（成功）或 1（失败）

sp_droplinkedsrvlogin 

删除运行 SQL Server 的本地服务器上的登录与链接服务器上的登录之间的现有映射。

语法

sp_droplinkedsrvlogin [ @rmtsrvname= ] 'rmtsrvname' ,   
   [ @locallogin= ] 'locallogin'

参数

[ @rmtsrvname = ] 'rmtsrvname' 应用登录映射的链接服务器的名称 SQL Server 。 rmtsrvname 的值为 sysname，无默认值。 rmtsrvname 必须已存在。

[ @locallogin = ] 'locallogin'SQL Server本地服务器上的登录名，它具有到链接服务器的映射 rmtsrvname。 locallogin 的值为 sysname，无默认值。 必须已经存在 locallogin 到 rmtsrvname 的映射。 如果为 NULL，则会删除 sp_addlinkedserver 创建的默认映射，该映射将本地服务器上的所有登录名映射到链接服务器上的登录名。

返回代码值

0（成功）或 1（失败）

sp_dropserver 

从本地 SQL Server 实例中的已知远程服务器和链接服务器的列表中删除服务器。

语法

sp_dropserver [ @server = ] 'server'   
     [ , [ @droplogins = ] { 'droplogins' | NULL} ]

参数

服务器
要删除的服务器。 server 的数据类型为 sysname，无默认值。 服务器 必须存在。

droplogins
指示如果指定了 droplogins ，则还必须删除 服务器 的相关远程服务器和链接服务器登录名。 @droplogins 为 char (10)，默认值为 NULL。

返回代码值

0（成功）或 1（失败）

示例

异地服务器, 数据库之间的查询

创建链接服务器

EXEC sp_addlinkedserver
@server = 'SourceHost', -- 目标服务器别名
@srvproduct = 'MSSQL',  -- 产品名称
@datasrc = '192.168.0.132\MSSQLSERVER2012' , -- 目标服务器名称
@provider = 'SQLOLEDB'

登录链接服务器

EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'SourceHost' , -- 与以上 @server 同名
@useself = 'false' ,
@locallogin = NULL ,
@rmtuser = 'sa' ,
@rmtpassword = '123'

删除登录与链接服务器的映射

--删除登录与链接服务器的映射
exec sp_droplinkedsrvlogin  @rmtsrvname= 'SourceHost' ,   
    @locallogin= 'locallogin'  

删除链接服务器

--删除链接服务器 
exec sp_dropserver 'SourceHost', 'droplogins'

查询异地服务器的目标数据库

select * from SourceHost.目标数据库名称.dbo.表名

转载请注明：清风亦平凡 » Microsoft SQL Server跨服务器查询

喜欢 (5)or分享 (0)

赏 支付宝扫码打赏 微信打赏