Link SQL Server 2008 to 2005

Have on hand:

1. A name for your linked server you want to create; can be anything, just keep it clean.
   I will call mine MyLinkedServer
   
   
2. If default instance, name of the server only (as opposed to server/instance)
   Mine is MyComputerName\SQLEXPRESS2
   
   
3. If second instance, server/instance_name
   
   
4. It is presumed you have same login name as the server you are reaching over and linking to.
   I tested these two servers on the same computer (SQL Server 2005 & 2008)
   If they are not, impersonate yourself to the server you are trying to talk to:
   
   1. After scripting this out, go to SMSS and right-click your near MyLinkedServer à Properties
      
   2. Select the security option
      
   3. Add the login name the other server uses and click impersonate.
      

   EXEC
   master.dbo.sp_addlinkedserver
   

   @server =
   N'MyLinkedServer',
   

   @srvproduct=N'',
   

   @provider=N'SQLNCLI',
   

   @datasrc=N'
   MyComputerName\SQLEXPRESS2'
   

If you use the graphical interface, go for it. This way is easier for me.

Now you can call whatever database you have there. Here's what I did:

select
*
from [MyLinkedServer].[MyDatabase].dbo.[MyTable]