连接来自不同服务器的表 [英] Joining tables from different servers

问题描述

对于如何在存储过程中连接来自不同服务器的表有什么建议吗?

Any suggestions how to join tables from different servers in stored procedure?

推荐答案

没有更多细节，很难给出直接的例子，但这里是基本思路:

Without more details, it's hard to give direct examples, but here is the basic idea:

首先，在存储过程之外，主机服务器(存储过程所在的服务器)必须知道第二台服务器，包括(可能)登录信息.

First, outside of the stored procedure, the host server (the server the stored procedure will be on) has to know about the second server, including (possibly) login information.

在您的主服务器上，运行 sp_addlinkedserver 存储过程.这只需要做一次:

On your main server, run the sp_addlinkedserver stored procedure. This only has to be done once:

exec sp_addlinkedserver @server='(your second server)';

如果您需要向第二台服务器提供登录信息(例如，该进程无法使用初始数据库连接中使用的相同凭据登录)，请使用sp_addlinkedsrvlogin 存储过程:

If you need to provide login information to this second server (for example, the process can't log in with the same credentials that are used in the initial database connection), do so with the sp_addlinkedsrvlogin stored proc:

exec sp_addlinkedsrvlogin @rmtsrvname='(your second server)',
@useself=false, 
@rmtuser='yourusername', 
@rmtpassword='yourpassword';

然后，在您的存储过程中，您可以在第二台服务器上指定表:

Then, in your stored procedure, you can specify tables on the second server:

SELECT table1.*
FROM table1
INNER JOIN [secondserver].[database].[schema].[table] AS table2 ON
    table1.joinfield = table2.joinfield

这篇关于连接来自不同服务器的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！