Chapter 8. Working with Data from Remote Data Sources

After completing this chapter, you will be able to:

• Open ad hoc connections to a variety of data sources using T-SQL

• Configure a linked server using T-SQL

• Configure a linked server using Microsoft SQL Server Management Studio

• Update data in remote data sources

In Chapter 4, "Transferring Your Database to Other Systems," you learned how to transfer local data to remote database servers, how to use the different replication options that SQL Server 2005 offers, and how to use SQL Server Integration Services to communicate with a variety of data sources.

In real-world applications, when you need to work with data from a remote data source, it is not always possible or desirable to set up a replication scheme; sometimes you just want to execute a single query or to extract the information in real time and not wait for replicated data.

In this chapter, we are going to focus on how to read data from remote data sources and how to write data to remote data sources in real time. These data sources can be either another SQL Server instance or a different data source, like a Microsoft Office Excel file or Microsoft Exchange Server.

When you want to read data from a remote data source, you need a way to declare the destination data source that you want to communicate with and what data you want to extract.

In applications that connect to a single database server, this is usually implemented using ADO.NET's Connection class. This chapter focuses on connecting to a remote data source from within SQL Server using T-SQL code when there is no ADO.NET available.