Accessing External Data

To access data from an OLE DB data source, provide Microsoft® SQL Server™ 2000 with the following information:

• The name of the OLE DB provider that exposes the data source.
  
  
• Any information 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, you can supply SQL Server with valid login IDs for the OLE DB data source.

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

Linked Server Names

A linked server is a virtual server that has been defined to Microsoft® SQL Server™ 2000 with all the information needed to access an OLE DB data source. 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 can then be referenced by using the linked server name:

• 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.
  
  
• 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 Names

An ad hoc name is used for infrequent queries against OLE DB data sources that are not defined as a linked server name. In SQL Server 2000, the OPENROWSET and OPENDATASOURCE functions provide connection information for accessing data from OLE DB data sources.

OPENROWSET and OPENDATASOURCE should be used 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, including security management and the ability to query catalog information. Each time these functions are called, all connection information, including passwords, must be provided.

OPENROWSET and OPENDATASOURCE appear to be functions; however, they are macros and do not support supplying Transact-SQL variables as arguments.

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:

• 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 OPENDATASOURCE function provides connection information as part of a four-part object name. This function supports only OLE DB providers that expose multiple rowsets using the catalog.schema.object notation. OPENDATASOURCE can be used in the same Transact-SQL syntax locations a linked server name can be used. OPENDATASOURCE is specified with:

• The name registered as the PROGID of the OLE DB provider used to access the data source.
  
  
• A connection string that specifies the various connection properties to be passed to the OLE DB provider. 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 it supports. For more information about connection strings, see OPENDATASOURCE.

Accessing Linked Servers

After a linked server is created using sp_addlinkedserver, it can be accessed using:

• Distributed queries. Accessing tables in the linked server through SELECT, INSERT, UPDATE, and DELETE statements using a linked server-based name.
  
  
• Remote stored procedures. Stored procedures can be executed against the linked server using a four-part name.

Servers running an instance of SQL Server can be defined as a remote server using sp_addserver. The remote server then can 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 2000, they may need to run for a period of time 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. Define one of the entries with a different server name, and use sp_setnetname to associate that definition with the network name of the remote server.

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