Linked Servers Inside SQL Server

I am trying to connect to a sql database on a different server say server2 and run a sql query on my server1. It will not run even though I specified tables like this [server2].[databasename].[dbo].[tablename] in my sql query. It gave me the following error:

Msg 7202, Level 11, State 2, Line 1
Could not find server 'server2' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

As the error specifies we have to add my server2 to a list of linked servers inside sql server.  So what is a linked server in SQL Server?  It allows us to execute remote queries on a remote data store [Sql server, oracle, file system, local Dsn, excel spreadsheet, active directory] by linking it to our server.  When you connect to your sql server inside management studio you will see a node named Linked Servers and there you can see a list of Providers for different types of data stores.  In the image you will see a list of linked servers if any and in my case I have added my server.

After adding a linked server I can query it by specifying entire name of your tables like I mentioned above.  You can also add Active Directory as a linked server and query Active Directory just like SQL Query.  Check this MSDN link to see which all data sources you can add as linked servers. 

The best way to add a linked server is through stored procedure specified in the error itself.  After adding a linked server you will also have to add a linked server login to connect to the remote database.  If you don’t specify security login to your linked server then it will try to connect with the credentials you are currently logged into your sql server through SSMS.  You can map a local user to the linked server to connect if only that user has local login access to that server.  For more check this msdn link on security of linked servers.

Syntax for adding a linked server and linked server login:

EXEC sp_addlinkedserver @server = 'server2', @srvproduct='', @provider='SQLNCLI', @datasrc = 'server2', @catalog='databasename'

EXEC sp_addlinkedsrvlogin @rmtsrvname='server2',@useself='FALSE', @rmtuser='remoteuserlogin',@rmtpassword='somecomplicatedpassword'

If you are using SQL server then you can leave @srvproduct as null.  

You can also connect to Oracle from SQL Server and here is this nice post  which details exact steps to do that.