Joining tables from different servers
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.
On your main server, run the sp_addlinkedserver stored procedure. This only has to be done once:
exec sp_addlinkedserver @server='(your second server)';
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
1. Check to see if you have any linked servers using exec sp_helpserver
2. If your server is not returned then it is not Linked
meaning you will need to add it. Otherwise move to step 3.
For Sql Server 2008 R2, go to Server Object > Linked Servers > Add new Linked Server
Or
exec sp_addlinkedserver @server='ServerName';
3. Connect to the Secondary server like so...
exec sp_addlinkedsrvlogin
@rmtsrvname='ServerName'
, @useself=false
, @rmtuser='user'
, @rmtpassword='Password';
4. Now you can Join the tables for the 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
You have to first link two servers before joining the tables. Once they are linked, you can just use the below query and replace server, database & table names.
Remember to execute the below sql in 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
Linking servers - http://msdn.microsoft.com/en-us/library/ms188279.aspx