Accessing External Data Using Distributed Queries

To access data from an OLE DB data source, Microsoft® SQL Server™ requires three or four pieces of information:

• The name of the OLE DB provider that exposes the data source.
• All the information the OLE DB provider needs to locate the source of the data.
• Optionally, valid login IDs for the OLE DB data source.
• 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.

There are two naming methods for accessing an OLE DB data source from SQL Server: the linked server name and the ad hoc computer name.

Linked server name

A linked server name is defined using the sp_addlinkedserver system stored procedure. The linked server definition contains all the information needed to locate the OLE DB data source. Local SQL Server logins are then mapped to logins in the linked server using sp_addlinkedsrvlogin. Remote tables from the linked server name can then be referenced in two ways:

• The linked server name can be used as the server name in a four-part name used as a table or view reference in a Transact-SQL statement. The other three parts of the name reference an object in the linked server that is exposed as a rowset.
• The linked server name can be used as an input parameter to an OPENQUERY function. OPENQUERY sends the OLE DB provider a command to execute. The returned rowset can then be used as a table or view reference in a Transact-SQL statement.

Ad hoc connector name

Ad hoc connector names are used for infrequent queries against OLE DB data sources that are not defined as a linked server name. An OPENROWSET function is specified with:

• All 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 OPENROWSET function, like the OPENQUERY function, can be used anywhere a table or view reference is used in a Transact-SQL statement.

Note The examples in this section use system stored procedures to configure linked servers because they succinctly show the parameters used. The SQL Server Enterprise Manager also supports configuring linked servers. For more information, see Configuring Linked Servers.

Defining a Server as both a Linked and Remote Server

Servers running SQL Server can be defined as a remote server using sp_addserver. The remote server can then be referenced in remote stored procedure calls. The remote server component is maintained as a compatibility feature for existing applications.

As applications are ported to SQL Server 7.0, they may need to run for a while with some new code using distributed queries against a linked server definition and some legacy code using a remote server definition. Both linked servers and remote servers use the same name space, so either the linked server or the remote server definition has to use a name that is different than the network name of the server being accessed remotely. This can be done by defining one of the entries with a different server name, then using sp_setnetname to associate that definition with the network name of the remote server.

This example shows defining both a linked server definition and a remote server definition that both access the same computer whose network name is othersite. The linked server definition uses the same name as the network name of the remote server, while the remote server definition uses another name.

/* Create a linked server definition to othersite. */

EXEC sp_addlinkedserver 'othersite', N'SQL Server'

/* Create a remote server definition using a

   pseudoname. */

EXEC sp_addserver 'RPCothersite'

/* Set the pseudoname to the network name faraway. */

EXEC sp_setnetname 'RPCothersite', 'othersite'

These two names can then be referenced in distributed queries or remote procedure calls.

/* A distributed query referencing othersite. */

SELECT *

FROM othersite.Northwind.dbo.Employees

/* A remote procedure call to the same server. */

EXEC RPCothersite.master.dbo.sp_who

/* Distributed queries can also be used to execute

   stored procedures on the other server. */

EXEC othersite.master.dbo.sp_who

Stored procedures executed using the remote server definition use the old remote stored procedure mechanism. Stored procedures executed using the linked server definition are executed as distributed queries through the OLE DB provider.