There may be times when you'll want to run an ad hoc query that retrieves data from a remote data source or bulk loads data into a SQL Server table. In such cases, you can use the OpenQuery,OpenRowSet or OpenDataSource function in Transact-SQL

This queries are connects to the destination server and runs the query on that server and returns the resultset. Whereas, in case of linked server query is executed on the local server 

openquery is much faster. Only one problem is the query string is limited to 8k only

See the below examples…

Format:

SELECT * FROM OPENQUERY(remotelinkedservername,Query)

Example:

SELECT * FROM OPENQUERY(GUNBATS,'select * from [BRETRACKER].[dbo].[ViQuality]' )

Format:

SELECT * FROM OPENDATASOURCE ( provider_name,
Connectionstring ).dbname.schemaname.TableName

Example:

SELECT * FROM OPENDATASOURCE('SQLOLEDB',
'Data Source=10.20.36.61;User ID=pfp;Password=xxxx').usrdb.dbo.T_pfpamount

Format:

SELECT * FROM OPENROWSET ( 'provider_name', { 'datasource' ; 'user_id' ; 'password' |
'Connectionstring' } , {[catalog. ] [ schema. ] object | 'query' } )

Example:

SELECT A.* FROM OPENROWSET ('SQLOLEDB','10.20.36.61' ; 'PFP' ;
'xxxx','select * from [usrdb].[dbo].[T_pfpamount]' 
) as a

When you try to execute queries involving OPENROWSET on SQL Server you get the error in some
places because of Ad Hoc Distributed Queries is disabled.

Following are the steps to enable Ad Hoc Distributed Queries on SQL Server:

sp_configure 'show advanced options',1
reconfigure
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

Ad Hoc Distributed Queries are now enabled. If you run the query with OPENROWSET
now you do not get the error again.


Linked Server

The Linked Servers option allows you to connect to another instance of SQL Server running
on a different machine

When you execute a distributed query (query a remote database) against a linked server, you
must include a fully qualified, four-part table name for each data source to query

i.e    linked_server_name.catalog.schema.object_name.

Example

SELECT * FROM [GUNBATS].[BRETRACKER].[dbo].[ViQuality] wher Quality = ‘Y’