One of the members in the SQL Server forum asked a question about querying the remote server without actually adding it as a Linked Server. One of the options to do this is to make use of the OPENDATASOURCE function

Assume that you have two servers Server1 and Server2. You want to query a table from Server2 from Server1. The usaul method is to add Server2 as a linked Server in Server1 using the sp_addLinkedServer system stored procedure. So now the query would be

Select columns from Server2.Db_name.owner_name.table_name

If you want to do it the same without actually adding it as Linked Server, use the following

If the remote server is of Windows Authentication

SELECT *FROM OPENDATASOURCE('SQLNCLI',
    'Data Source=Server2;Integrated Security=SSPI')
    .test.dbo.MyTable

If the remote server is of SQL Server Authentication

SELECT   * FROM OPENDATASOURCE(
            'SQLOLEDB',
            'Data Source=server2;user id=user_name;password=pass_word'
        ).test.dbo.MyTable

Also note that you can query a table from MS Access database or and EXCEL file also using the OPENDATASOURCE function