Join tables from different servers

Question

Any suggestions for joining tables from different servers in a stored procedure?

Answer 1

Without details, it is difficult to give direct examples, but here is the basic idea:

First, outside the stored procedure, the host server (the server on which the stored procedure will be turned on) must know about the second server, including (possibly) login information.

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

exec sp_addlinkedserver @server=’(your second server)‘;

(, , ), sp_addlinkedsrvlogin proc:

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

:

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

Answer 2

, . , , .

sql DB2:

EXEC sp_addlinkedserver DB1
GO

-- below statement connects sa account of DB2 to DB1
EXEC sp_addlinkedsrvlogin @rmtsrvname = 'DB1', @useself = 'false', @locallogin = 'sa', @rmtuser = 'sa', @rmtpassword = 'DB1 sa pwd'
GO

SELECT a.columns 
FROM DB1.database_name.dbo.table_name a
INNER JOIN DB2.database_name.dbo.table_name b
ON a.columnId = b.columnId
GO

- http://msdn.microsoft.com/en-us/library/ms188279.aspx

Answer 3

1 .. Check if you have any related servers withexec sp_helpserver

2. If your server does not return, this does not mean Linkedthat you will need to add. Otherwise, go to step 3.

For Sql Server 2008 R2, go toServer Object > Linked Servers > Add new Linked Server

or

exec sp_addlinkedserver @server='ServerName'; 

3. Connect to a secondary server like this ...

exec sp_addlinkedsrvlogin 
@rmtsrvname='ServerName'
, @useself=false
, @rmtuser='user'
, @rmtpassword='Password';

4. Now you can join tables for two different servers.

SELECT 
    SRV1.*
FROM 
    DB1.database_name.dbo.table_name SRV1
        INNER JOIN DB2.database_name.dbo.table_name SRV2
        ON SRV1.columnId = SRV2.columnId
GO