In Focus: Linked Servers and Distributed Heterogeneous Queries

Linked servers are a new feature in SQL Server 7.0 that lets you do distributed heterogeneous queries. (Distributed means on multiple machines and heterogeneous means different data sources. SQL Server 6.5 supported heterogeneous replication, but not heterogeneous queries). Assume you have customer service data stored in SQL Server, historical data stored in a Microsoft Access or Excel database, and transactional data stored in an Oracle server. How can you join tables from each data source in the same query?

With distributed queries, you can create two linked servers in your SQL Server that represent the Oracle and Access data sources. Then, you can reference tables within those distributed heterogeneous data sources as though they were local tables within the SQL Server. You can issue SQL Server one query, which knows how to retrieve the Access and Oracle data. SQL Server packages all the data into one result set and returns the answer to the end users, who never need to know what happened behind the scenes. This feature is similar to attached tables in Access, and you know how handy this capability can be.

SQL Server 7.0 supports distributed queries through Object Linking and Embedding Database (OLE DB), Microsoft's new API for Universal Data Access. (For more information, see http://www.microsoft.com/data.) The best way to understand OLE DB is by comparing it with Open Database Connectivity (ODBC), which has become the primary Universal Data Access mechanism for relational data sources. Although ODBC had its share of naysayers when Microsoft first launched it 5 years ago, ODBC has had a huge impact on the database business.

But a great deal of interesting and useful corporate data isn't in relational databases--or any database management systems (DBMSs), for that matter. Most major DBMS vendors (Oracle, IBM, Informix, and Sybase) are modifying their relational database engines to store that nontraditional data. Microsoft's approach, however, is to leave the data where it is and use OLE DB to provide access to the data. Theoretically, OLE DB does for any data source what ODBC did for the relational world. It lets developers use one API to access heterogeneous data stores--audio, video, full text, and messaging.

Microsoft designed distributed queries to work with any OLE DB provider, but for SQL Server 7.0 beta 3, Microsoft has said that the only tested and supportedproviders are SQL Server, ODBC, Access (technically the Jet database engine), and Oracle.

Here's how you set up a linked server against an Access filemaster database data source. The first command creates a linked server within SQL Server so

that SQL Server knows where the remote data is stored and what type of OLE DB provider to use when SQL Server accesses it.

sp_addlinkedserver
@server = "Northwinds"
@srvproduct = "Access 97",
@Provider = "Microsoft.Jet.OLEDB.3.51",
@datasrc = "c: nwind.mdb"

The next command creates a login map for Access. SQL Server knows that local users known as sa log in to the Access database as the remote user Admin.

exec sp_addlinkedsrvlogin
@rmtsrvname = "Northwinds",
@useself = "false",
@locallogin = "sa",
@rmtuser = "Admin",
@rmtpassword = NULL

Now, execute the query

SELECT * FROM Northwinds...customers

and you'll see data coming back from the Northwinds server where the familiar Northwind database (NWIND.MDB) is located. Congratulations, you've just written your first distributed heterogeneous query!

SQL Server 7.0 provides two new commands called OPENROWSET and OPENQUERY that let you issue pass-through queries to the remote OLE-DB datasource. Because OPENROWSET includes all connection information necessary to access remote data from an OLE DB data source, it's useful for ad hoc connections using OLE DB. You can even reference it in the FROM clause of a query as though it were a table name:

OPENROWSET('provider_name'
{
'datasource';'user_id';'password'
| 'provider_string'
},
{
[catalog.][schema.]object
| 'query'
})

OPENQUERY is even easier to use. It executes the specified pass-through query on the given linked_server, which is an OLE DB data source:

OPENQUERY(linked_server, 'query')