The Real MTCS SQL Server 2008 Exam 70/432 Prep Kit- P72: Congratulations on your journey to become certified in SQL Server 2008. This book will help prepare you for your exam and give you a practical view of working with SQL Server 2008. | ETL Techniques Chapter 8 337 In the last few sections we have discussed a broad range of bulk load operations. The BCP and BULK INSERT statements both assume that you will be working with flat files and SQL Server. Sometimes the data you want is already in another database engine somewhere else however and you would like to get it straight from that database rather than having to go through a flat file as an intermediary. Distributed queries are one way to do that and we ll talk about those next. Distributed Queries Distributed queries make it possible for you to have SQL Server work with data in external data sources. The external data sources could be other SQL Server instances or they could be an Oracle instance or an Excel file and so on. There are a number of methods available to working with data in external data sources. You can specify the data source as part of the query using functions like OPENROWSET or OPENDATASOURCE. Because these methods specify the data connection with the query and not as a preconfigured server object we call them ad hoc distributed queries. You can also formally define the external data sources before you use them by defining linked servers. These linked servers can be used in four-part fully qualified object names to directly reference tables and views in the external data sources. We will talk about ad hoc distributed queries and linked server but first let s quickly cover the use of fully qualified names. Understanding Fully Qualified Object Names The fully qualified name of an object in SQL Server is actually made up of four parts with each part separated by a period. The format of a fully qualified name is InstanceName . DatabaseName . SchemaName .ObjectName The InstanceName is optional. If you don t specify it it assumes the instance that the client is connected to. In fact initially you can use the name of only the SQL Instance to which you connected. However through the use of Linked Servers or the OPENDATASOURCE function .
