Microsoft SQL linked servers - a couple of gotcha's

These are very easy to set so that one instance of SQL server can access another however there were a couple of traps that caught me out:

1) You need to set up a ODBC connection for the server (for my simplicity I have used the name for the server as the data source name)

2) You need to do both a linked server stored procedure and a linked server login

3) You need to use a fully 4 part qualified name to get a result e.g. select count(*) from tiger1...all  will fail whereas select count(*) from tiger1.funds.dbo.all will work.

Here is my test code for setting them up - the sql server name is tiger1.  I haven't shown the ODBC setup.

EXEC master.dbo.sp_droplinkedsrvlogin @rmtsrvname=N'tiger1',@locallogin=NULL
EXEC sp_dropserver   @server='tiger1'
EXEC sp_addlinkedserver  
   @server='tiger1',
   @srvproduct='',
   @provider='SQLNCLI',
   @datasrc='tiger1',
   @catalog='Funds'

EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname=N'tiger1',
    @useself=N'False',
    @locallogin=NULL,
    @rmtuser=N'sa',
    @rmtpassword=''
exec sp_linkedservers

select count(*) from all
select count(*) from arundel01.Funds.dbo.all