In my previous article, I’ve explained the select query joining tables from different database on the same SQL Server. In this article, I’ll explain joining tables from databases on different SQL Servers.
There are 2 steps to join tables from different servers. The first step is to link the SQL Servers. The next and the last step is to join the tables using the select query having the server name as prefix for the table name.
1. Linking The SQL Servers
The first step is to link the SQL Servers (or instance) using the system stored procedures sp_addlinkedserver and sp_addlinkedsrvlogin. The system stored procedure sp_addlinkedserver is used to link the remote server and sp_addlinkedsrvlogin is used to configure the linked remote server to use the local login credentials
A basic syntax for using sp_addlinkedserver and sp_addlinkedsrvlogin is:
sp_addlinkedserver @Server, @SrvProduct;
sp_addlinkedsrvlogin @RemoteServerName, @LocalLogin, @UseSelf;
For our example, I’m linking the secondary named instance of the SQL Server [MACWINDOWS\MSSQLSERVER2] with my primary or default instance [MACWINDOWS]. The sample code for creating the linked server will be like this:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
@server = N'MACWINDOWS\MSSQLSERVER2',
@srvproduct=N'SQL Server' ;
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'MACWINDOWS\MSSQLSERVER2',
@locallogin = NULL,
@useself = N'True' ;
GO
More details about linked servers and using sp_addlinkedserver and sp_addlinkedsrvlogin is available here.
2. Joining Tables Between Linked SQL Servers
Once the remote server is linked, then you can join tables from the linked remote server with the local server by having the server name as prefix for the table name. here is the sample SQL script for joining tables from databases on different SQL Servers:
SELECT
t1.SlNo,
t2.FirstName,
t2.LastName
FROM
[MACWINDOWS\MSSQLSERVER2].[MtbDbTwo].[dbo].[TableTwo] as t2
INNER JOIN [MtbDbOne].[dbo].[TableOne] as t1 on t1.SlNo = t2.SlNo;
GO
The sample queries mentioned in this article are tested and working good on SQL Server 2014 Developer Edition.
I know this is an old post but the four part naming method can cause some really big performance issues. It’s better to access remote data using the openquery method.
Here is another article supporting my opinion:
https:// maxteo.wordpress.com/2009/07/29/openquery-vs-4-part-name-query/
Like Brent Ozar says, Mileage may vary so test both ways.
I got this error while trying to execute the store proc link server with login
The server ‘abc\xyz’ does not exist. Use sp_helpserver to show available servers.
Please see if the server name and the instance names are not misspelled, then try using sp_helpserver to confirm if the server is accessible.