I keep losing this helpfile.. when I need it the most, it is located in the BOL labeled "sp_addlinkedserver (Transact-SQL)", if you scroll to the bottom of the article.

The frustrating thing about learning about linked servers is the coordination between the different types of connections! Viva la difference?

Remote OLE DB data source	OLE DB provider (@srvproduct)	product_name (whatever you want to name it "blah blah"…)	provider_name (@provider)	data_source (@datasrc)	location (anyone?)	provider_string (@providerstr?)	catalog (@catalog)
SQL Server	Microsoft SQL Server Native Client OLE DB Provider	SQL Server 1 (default)
SQL Server	Microsoft SQL Server Native Client OLE DB Provider		SQLNCLI	Network name of SQL Server (for default instance)			Database name (optional)
SQL Server	Microsoft SQL Server Native Client OLE DB Provider		SQLNCLI	servername\instancename (for specific instance)			Database name (optional)
Oracle	Microsoft OLE DB Provider for Oracle	Any2	MSDAORA	SQL*Net alias for Oracle database
Oracle, version 8 and later	Oracle Provider for OLE DB	Any	OraOLEDB. Oracle	Alias for the Oracle database
Access/Jet	Microsoft OLE DB Provider for Jet	Any	Microsoft.Jet. OLEDB.4.0	Full path of Jet database file
ODBC data source	Microsoft OLE DB Provider for ODBC	Any	MSDASQL	System DSN of ODBC data source
ODBC data source	Microsoft OLE DB Provider for ODBC	Any	MSDASQL			ODBC connection string
File system	Microsoft OLE DB Provider for Indexing Service	Any	MSIDXS	Indexing Service catalog name
Microsoft Excel Spreadsheet	Microsoft OLE DB Provider for Jet	Any	Microsoft.Jet. OLEDB.4.0	Full path of Excel file		Excel 5.0
IBM DB2 Database	Microsoft OLE DB Provider for DB2	Any	DB2OLEDB			See Microsoft OLE DB Provider for DB2 documentation.	Catalog name of DB2 database

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]