SQL Linked Servers

One thing that I do from time to time is work with 2 different instances of the same DB on different servers e.g. when comparing data on 2 different versions of the application in development and QA. While I could easily query the tables I'm interested in 2 different SQL query editor windows sometimes its convenient if I could treat the tables from the different databases as belonging to the same server so I don't have to switch between multiple windows. The other benefit of this is being able to perform joins and other update operations.

Linked servers enable me to do just do that. It's easy to add a linked server in SQL Server by using the following sproc:

sp_addlinkedserver [server]

There are some other params that can be supplied to the add sproc if you are dealing with non-SQL server but if you mainly deal with SQL Servers then your life is much easier. To display the current linked servers use the following sproc:

sp_linkedservers

To delete use: sp_dropserver [server]

Now comes the fun part - how do we actually query data from a linked server. It's pretty simple as long as you follow the proper naming format:

select * from [server].[linkedserverdb].[dbo].[table]

So, for example, if I wanted to perform a diff on the Client table in different databases then I would do:

select a.clientid, b.clientid from [dbo].[Client] as a

full join [server].[Northwind].[dbo].[Client] as b

on a.ClientId = b.ClientId

This will display the same and missing client IDs in both tables.