Microsoft SQL Server 2012 Bible

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 standard 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 ...