Microsoft® SQL Server® 2008 Bible

Chapter 31. Executing Distributed Queries

IN THIS CHAPTER

• Understanding distributed queries

• Making the connection with remote data sources

• T-SQL distributed queries

• Pass-through queries

• Two-phase commits and distributed transactions

Data is seldom in one place. In today's distributed world, most new projects enhance, or at least connect to, existing data. That's not a problem; SQL Server can read and write data to most other data sources. Heterogeneous joins can even merge SQL Server data with an Excel spreadsheet.

SQL Server offers several methods for accessing data external to the current database. From simply referencing another local database to executing pass-through queries that engage an Oracle server, SQL Server can handle it.

Distributed Query Concepts

Linking to an external data source is nothing more than configuring the name of the linked server, along with the necessary location and login information, so that SQL Server can access data on the linked server.

Linking is a one-way configuration, as illustrated in Figure 31-1. If Server A links to Server B, then it means that Server A knows how to access and log into Server B. As far as Server B is concerned, Server A is just another user.

If linking a server is a new concept to you, then it could easily be confused with registering a server in Management Studio. As illustrated in Figure 31-1, Management Studio is only communicating with the servers as a client application. Linking the servers enables SQL Server instance A to communicate ...