Linking SQL Servers30 June 2017
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
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:
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'