Lesson 2:Using Distributed Queries to Access External Data | MCSE Training Kit (Exam 70-229): Microsoft SQL Server(TM) 2000 Database Design and Implementation (Pro Certification)

3 4

By using distributed queries, you can access data stored in multiple instances of SQL Server and heterogeneous data stored in various relational and non-relational data sources. SQL Server 2000 supports distributed queries through the use of OLE DB, the Microsoft specification of an Application Programming Interface (API) for universal data access. In this lesson, you will learn how to use distributed queries to access data in external data sources. The lesson focuses on how to use linked servers for frequent queries against OLE DB data sources and how to use ad hoc names for infrequent queries against OLE DB data sources.

After this lesson, you will be able to:

Use linked servers, Transact-SQL statements, and the OPENQUERY function to access external data.
Use ad hoc computer names and the OPENROWSET and OPENDATASOURCE functions to access external data.

Estimated lesson time: 30 minutes

Introduction to Distributed Queries

In SQL Server 2000, a distributed query is a single query that can access data from multiple data sources, including distributed data and heterogeneous data. Distributed queries can access data from external sources by using OLE DB. OLE DB providers expose data in tabular objects called rowsets. SQL Server 2000 enables rowsets from OLE DB providers to be referenced in Transact-SQL statements as if they were SQL Server tables.

Tables and views in external data sources can be referenced directly in SELECT, INSERT, UPDATE, and DELETE Transact-SQL statements. Because they use OLE DB as the underlying interface, distributed queries can access traditional RDBMS systems having SQL query processors as well as data that is managed by data sources of varying capabilities and sophistication. As long as the software owning the data exposes it in a tabular rowset through an OLE DB provider, the data can be used in distributed queries.

NOTE

Using distributed queries in SQL Server is similar to the linked table functionality through ODBC, which Microsoft Access previously supported. This functionality is now built into SQL Server with OLE DB as the interface to external data.

To access data from an OLE DB data source, you must provide SQL Server with the following information:

The name of the OLE DB provider that exposes the data source
Any information that the OLE DB provider needs to locate the source of the data
Either the name of an object that the OLE DB data source can expose as a rowset, or a query that can be sent to the OLE DB provider that will cause it to expose a rowset. (The objects that can be exposed as rowsets are known as remote tables. The queries that generate rowsets are known as pass-through queries.)
Optionally, valid login IDs for the OLE DB data source

SQL Server 2000 supports two methods for referencing heterogeneous OLE DB data sources in Transact-SQL statements: the linked server name and the ad hoc computer name.

Using Linked Server Names in Distributed Queries

You can use linked server names in Transact-SQL statements to identify external data sources. Once the linked server is defined, the linked server name can be used as the server name in a four-part name (in a table or view reference) or as an input parameter to an OPENQUERY function.

Linked Servers

A linked server is a virtual server that has been defined in SQL Server. The linked server definition includes all of the information needed to access an OLE DB data source. You can set up a linked server by using Enterprise Manager or by using the sp_addlinkedserver system stored procedure. The linked server definition contains all of the information needed to locate the OLE DB data source.

NOTE

Linked server names are most often referenced in distributed queries. Remote stored procedures can also access linked servers, however. Stored procedures can be executed against the linked server by using a four-part name.

A linked server definition specifies an OLE DB provider, such as Microsoft OLE DB Provider for SQL Server, and an OLE DB data source, such as an instance of SQL Server. An OLE DB provider is a dynamic link library (DLL) that manages and interacts with a specific data source. An OLE DB data source identifies the specific database that is accessible through OLE DB. Although data sources that are queried through linked server definitions are usually databases, OLE DB providers exist for a wide variety of files and file formats, including text files, spreadsheet data, and the results of full-text content searches.

Four-Part Names

After a linked server is defined, a four-part name in the form linked_server_name.catalog.schema.object_name can be used in Transact-SQL statements to reference data objects in that linked server. The following table describes the parts of a four-part name:


Part Name	Description
linked_server_name	Linked server referencing the OLE DB data source
catalog	Catalog in the OLE DB data source that contains the object
schema	Schema in the catalog that contains the object
object_name	Data object in the schema0

SQL Server uses the linked server name to identify the OLE DB provider and the data source. The catalog, schema, and object_name parameters are passed to the OLE DB provider to identify a specific data object. When the linked server refers to an instance of SQL Server, catalog refers to a database and schema refers to an owner ID.

The following SELECT statement returns all rows from the Contacts table:

 SELECT *  FROM StoreOwners...Contacts

This SELECT statement uses the StoreOwners linked server name to identify the OLE DB data source. Notice that the linked server name and the object name are connected by three periods (StoreOwners...Contacts) that serve as placeholders for the catalog and schema.

The OPENQUERY Function

The OPENQUERY function is used to execute the specified pass-through query on the given linked server, which is an OLE DB data source. The OPENQUERY function can be referenced in the FROM clause of a query as though it were a table name. The OPENQUERY function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement (subject to the capabilities of the OLE DB provider). Although the query might return multiple result sets, OPENQUERY returns only the first one.

The following SELECT statement returns the LastName values in the contacts table:

 SELECT *  FROM OPENQUERY (StoreOwners, 'SELECT LastName FROM Contacts')

This SELECT statement uses the OPENQUERY function to retrieve data from an external data source. The linked server name is one of the parameters of the OPENQUERY function.

Using Ad Hoc Computer Names in Distributed Queries

You can use an ad hoc computer name for infrequent queries against OLE DB data sources that are not defined with a linked server name. In SQL Server 2000, the OPENROWSET and OPENDATASOURCE functions provide connection information for accessing data from OLE DB data sources.

You should use the OPENROWSET and OPENDATASOURCE functions only to reference OLE DB data sources that are accessed infrequently. For any data sources that will be accessed more than a few times, define a linked server. Neither OPENDATASOURCE nor OPENROWSET provide all of the functionality of linked server definitions, such as security management or catalog information queries. Each time these functions are called, all connection information, including passwords, must be provided.

Although OPENROWSET and OPENDATASOURCE appear to be functions and are referred to as such, they are technically not functions. OPENROWSET and OPENDATASOURCE are macros and do not support supplying Transact-SQL variables as arguments.

The OPENROWSET Function

The OPENROWSET function can be used with any OLE DB provider that returns a rowset and can be used anywhere a table or view reference is used in a Transact-SQL statement. OPENROWSET is specified with the following information:

All of the information needed to connect to the OLE DB data source
Either the name of an object that will generate a rowset or a query that will generate a rowset

The following SELECT statement uses the OPENROWSET function to connect to a Microsoft Access database file and access data in the Contacts table:

 SELECT *  FROM OPENROWSET ('Microsoft.jet.oledb.4.0',  'C:\StoreOwners.mdb'; 'admin'; '', Contacts)

In this SELECT statement, the OLE DB provider is Microsoft.jet.oledb.4.0; the data source is C:\StoreOwners.mdb; the user ID is admin; and there is no password.

The OPENDATASOURCE Function

The OPENDATASOURCE function provides connection information as part of a four-part object name. This function supports only OLE DB providers that expose multiple rowsets that use the catalog.schema.object notation. OPENDATASOURCE can be used in the same Transact-SQL syntax locations where a linked server name can be used. OPENDATASOURCE is specified with the following information:

The name registered as the PROGID of the OLE DB provider that is used to access the data source
A connection string that specifies the various connection properties to be passed to the OLE DB provider

NOTE

The connection string syntax is a sequence of keyword-value pairs. The basic syntax is defined in the Microsoft Data Access Software Development Kit, and each provider documents the specific keyword-value pairs that it supports.

The following SELECT statement uses the OPENDATASOURCE function to connect to a Microsoft Access database file and access data in the Contacts table:

 SELECT *  FROM OPENDATASOURCE ('Microsoft.Jet.oledb.4.0',  'Data Source = c:\StoreOwners.mdb; User ID = Admin; Password = ') ...Contacts

In this SELECT statement, the OLE DB provider is Microsoft.jet.oledb.4.0; the data source is C:\StoreOwners.mdb; the user ID is admin; and there is no password.

Exercise 2: Using Distributed Queries to Access External Data

In this exercise, you will define a linked server and then use Transact-SQL SELECT statements to connect an external data source. To complete this exercise, you must copy the Test1.mdb file in the \Chapter07\Exercise2 folder of the Training Kit Supplemental CD-ROM to the root directory of the C: drive of your Windows 2000 computer. In addition, you should be logged into your Windows 2000 Server computer as Administrator.

NOTE

You are not limited to the C: drive or to the root folder. You can select another drive or folder in which to store the files as long as you can access that file through the linked server and Transact-SQL statements.

To define a linked server

Open Enterprise Manager and expand the Security tab of the local database.
Right-click the Linked Servers node, then click New Linked Server.

The Linked Server Properties - New Linked Server dialog box appears.

Select the Other Data Source radio button.

Many of the options on the bottom half of the dialog box become active.

In the Linked Server text box, type TEST_SERVER.
In the Provider Name drop-down list, select Microsoft Jet 4.0 OLE DB Provider.
In the Data Source text box, type C:\Test1.mdb.
Click OK.

The Test_Server linked server is added to the console tree.

To use an OPENQUERY function to query an external data source

Open Query Analyzer and connect to your local server.
In the Editor pane of the Query window, enter the following Transact-SQL code:

 SELECT *  FROM OPENQUERY (Test_Server, 'SELECT FirstName, LastName FROM TestTable1')

In this statement, you are using the OPENQUERY function to identify the linked server (Test_Server) and to define a SELECT statement that returns the FirstName and LastName values from the TestTable1 table.

Execute the Transact-SQL statement.

The result set appears in the Grids tab of the Results pane.

To use a Transact-SQL statement to query an external data source

In the Editor pane of the Query window, enter the following Transact-SQL code:

 SELECT FirstName, LastName  FROM Test_Server...TestTable1

In this statement, you are identifying the linked server as part of a four-part name. Notice that three periods (...) are used as placeholders for two of the parts. This statement returns the same result as that returned by the statement in the previous procedure.

Execute the Transact-SQL statement.

The result set appears in the Grids tab of the Results pane.

To use the OPENROWSET function to query an external data source

In the Editor pane of the Query window, enter the following Transact-SQL code:

 SELECT FirstName, LastName  FROM OPENROWSET ('Microsoft.jet.oledb.4.0', 'C:\Test1.mdb';  'admin'; '', TestTable1)

In this statement, you are using an OPENROWSET function to return the FirstName and LastName values from the TestTable1 table. Note that the OLE DB provider is Microsoft.jet.oledb.4.0; the data source is C:\Test1.mdb; the user ID is admin; and there is no password.

Execute the Transact-SQL statement.

The result set appears in the Grids tab of the Results pane.

Lesson Summary

By using distributed queries, you can access data stored in multiple instances of SQL Server and heterogeneous data stored in various relational and non-relational data sources. Distributed queries can access data from external sources by using OLE DB. SQL Server 2000 supports the methods for referencing heterogeneous OLE DB data sources in Transact-SQL statements: the linked server name and the ad hoc computer name. You can use linked server names in Transact-SQL statements to identify external data sources. Once the linked server is defined, the linked server name can be used as the server name in a four-part name in a table or view reference or as an OPENQUERY input parameter. In addition, an ad hoc computer name can be used for infrequent queries against OLE DB data sources that are not defined with a linked server name. In SQL Server 2000, the OPENROWSET and OPENDATASOURCE functions provide connection information for accessing data from OLE DB data sources.