Microsoft® SQL Server® 2012 Bible

377

Chapter 15: Executing Distributed Queries

15

AROUT Around the Horn Thomas Hardy

BERGS Berglunds snabbkop Christina Berglund

BLAUS Blauer See Delikatessen Hanna Moos

Developing Distributed Queries

After you establish the link to the external data source (whether it’s via Linked Servers in

Management Studio or via T-SQL), SQL Server can reference the external data using stan-

dard queries. Querying external data is as simple as using the four available basic syntax

methods, depending on the link setup.

As you saw earlier, you can query a data source using the four-part name if it is a local

server, or you can use the four-part name using the OpenQuery() pass-through if it is an

external data source.

For Ad Hoc queries (in which the Link is declared in the query), look at a couple of built-in

T-SQL functions that provide remote access to data from OLE DB data sources:

■ (^) OPENDATASOURCE(): Provides ad hoc connection information as part of a four-part
name without using a linked server name
■ (^) OPENROWSET(): Includes the connection information necessary to access remote
data from an OLE DB data source
In this section you look at how to use these two functions to execute distributed queries
both locally and in a pass-through fashion.
Distributed Queries and SSMS
SQL Server Management Studio doesn’t contain a graphical way to executing distributed
queries, and it isn’t possible to drag a linked server or remote table into the query designer.
You can, however, enter the distributed query manually then execute it. It is also possible
to drag the name of the linked server from Object Explorer to a query window.
Distributed Views
Views are saved T-SQL SELECT statements and are very useful for ad hoc queries and pre-
venting user access directly to the underlying table. They are also good, however, for dis-
tributing queries. Wrapping a distributed query inside a view allows users and developers
alike the ability to execute distributed queries when they are unfamiliar with the different
methods for executing distributed queries.
Local Distributed Queries
A local-distributed query sounds like an oxymoron, but it is far from it. A local
distributed query is a query in which the external data is pulled into the local SQL Server
c15.indd 377c15.indd 377 7/30/2012 4:50:40 PM7/30/2012 4:50:40 PM
http://www.it-ebooks.info