Josh and KD Curtiss

Linking SQL Servers

Linking SQL servers helps you to write queries that join tables from other databases even if the database is on another server. Very handy. For instance, if you’re writing a query from serverA and you want to join a table from myOtherDB on serverB:

SELECT *
FROM   myTable a join [serverB].myOtherDB.dbo.myOtherTable b on a.id=b.id
WHERE  etc etc etc

This makes sense, but it doesn’t happen automatically. You have to let your server know that its ok to link to the other server.

To accomplish this, use sp_addlinkedserver:

sp_addlinkedserver @server='serverB'

You may then have to define what credentials to use with sp_addlinkedsrvlogin. You can either tell it to pass your existing credentials or you can define an entirely alternate set of credentials for the other server. Very handy if that scenario exists.

-- Pass existing credentials:
sp_addlinkedsrvlogin @rmtsrvname='serverB', @useself='TRUE'
-- Or explicitly define new credentials:
sp_addlinkedsrvlogin @rmtsrvname='serverB', @useself='FALSE', @rmtuser='myusername', @rmtpassword='mypassword'