SqlServer远程连接使用sp_addlinkedserver

sp_addlinkedserver 是 SQL Server 中的一个系统存储过程,用于创建到远程 SQL Server 实例的链接服务器定义。

链接服务器允许 SQL Server 访问远程数据源,如其他 SQL Server 实例、Oracle 数据库、Access 数据库等。通过创建链接服务器,你可以在本地 SQL Server 实例上查询和操作远程数据源中的数据,就像访问本地数据一样。

sp_addlinkedserver 存储过程的语法如下:

sp_addlinkedserver

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

其中:

@server: 指定链接服务器的名称。
@srvproduct: 指定远程数据源的产品名称,如 'SQL Server'。
@provider: 指定用于连接远程数据源的 OLE DB 提供程序名称。
@datasrc: 指定远程数据源的名称。
@location: 指定远程服务器的网络地址。
@provstr: 指定连接到远程数据源时使用的连接字符串。
@catalog: 指定要连接到的远程数据库或目录的名称。
例如,要创建到名为 RemoteServer 的 SQL Server 实例的链接服务器,可以使用以下语句:

EXEC sp_addlinkedserver

@server = N'RemoteServer', 
@srvproduct = N'SQL Server',
@provider = N'SQLNCLI11', 
@datasrc = N'RemoteServer\Instance1';

在创建链接服务器后,你就可以在本地 SQL Server 实例中访问远程数据源中的数据,就像访问本地数据一样。例如:

SELECT *
FROM RemoteServer.DatabaseName.SchemaName.TableName;
sp_addlinkedserver 存储过程提供了一种方便的方式来集成来自不同数据源的数据,支持分布式查询和数据操作。但是,由于涉及网络通信,跨服务器查询可能会影响性能,因此在生产环境中使用时需要谨慎评估。

要查看已经创建的链接服务器,可以使用以下几种方法:

查询系统视图 sys.servers
SELECT * FROM sys.servers;
此视图包含有关已创建的链接服务器的信息,如服务器名称、产品名称、提供程序名称等。

使用存储过程 sp_linkedservers
EXEC sp_linkedservers;
此存储过程将返回一个结果集,列出所有已创建的链接服务器及其属性。

在 SQL Server Management Studio (SSMS) 中查看
在 SSMS 的对象资源管理器中,展开"服务器对象"节点,你会看到"链接服务器"文件夹。展开该文件夹,你可以看到所有已创建的链接服务器。右键单击链接服务器,选择"属性",可以查看该链接服务器的详细信息。

查询系统表 sys.servers 和 sys.linked_logins
SELECT

srv.name AS 'Linked Server', 
srv.product AS 'Product Name',
srv.provider AS 'Provider Name',
srv.data_source AS 'Data Source',
ll.remote_name AS 'Remote User'

FROM sys.servers srv
LEFT JOIN sys.linked_logins ll ON srv.server_id = ll.server_id;
此查询将返回已创建的链接服务器及其关联的远程登录名。

通过上述方法,你可以查看已创建的链接服务器的名称、产品名称、提供程序名称、数据源等详细信息。这对于管理和维护链接服务器非常有用