Retrieve data from remote server in SQL Server!

Question

Is there any other way to retrieve data from a remote server in SQL Server 2005 instead of using linked server?

Answer 1

You can use OPENDATASOURCE.

Example from linked page:

SELECT *
FROM OPENDATASOURCE('SQLNCLI',
    'Data Source=London\Payroll;Integrated Security=SSPI')
    .AdventureWorks.HumanResources.Employee

Answer 2

What exactly do you need to replace the linked server for? Using ad-hoc remote queries via OPENQUERY or OPENDATASOUCE is exactly the same as using a linked server. They offer no advantage whatsoever over linked server, just disadvantages: no granular access control, no proxy credentials definition, and add code maintenance problems when the remote server relocates.

Alternatives to linked servers (or the ad-hoc equivalent) are to either expose a copy of the data (replication, log shipping, hardware disk replication etc) or expose the data over some other channel like web services or Service Broker.

Answer 3

SQL Server Integration Services, provided the retrieval can be scheduled / async and not real-time.