CODEWITHSUNDEEP
sql-serverlinked-server
Fire Hand
Fire Hand

Reputation: 26366

Retrieve data from remote server in SQL Server!

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

Upvotes: 2

Views: 3005

Answers (3)

onupdatecascade
onupdatecascade

Reputation: 3366

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

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294287

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.

Upvotes: 1

tvanfosson
tvanfosson

Reputation: 532465

You can use OPENDATASOURCE.

Example from linked page:

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

Upvotes: 5

Related Questions