SQL Server 2005 Linked Server using TSQL

Use the below to commands to add a server as linked server:

To Add the Server:
sp_addlinkedserver 'Alias Name', '', 'SQLNCLI', NULL, NULL, 'SERVER=IP', NULL

To Add the security:
sp_addlinkedsrvlogin 'Alias Name', 'false', NULL, 'User Name', 'Password'

NOTE: When you are trying to connect SQL Server 2000 to SQL Server 2005 as Linked server you may get the following error:

OLE DB provider "SQLNCLI" for linked server "serv01" returned message "Unspecified error".

OLE DB provider "SQLNCLI" for linked server "serv01" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".

Msg 7311, Level 16, State 2, Line 1

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "serv01". The provider supports the interface, but returns a failure code when it is used.

Solution is specified in this link.

What worked for me is this

SELECT * FROM OPENQUERY(LinkedServerAlias,'SELECT * FROM [Fully Qualified Name')