Configuring Linked Servers

A linked server configuration allows Microsoft® SQL Server™ to execute commands against OLE DB data sources on different servers. Linked servers offer these advantages:

• Remote server access.
• The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
• Freedom from the need to address diverse data sources differently.

Linked Server Components

A linked server definition specifies an OLE DB provider and an OLE DB data source.

An OLE DB provider is a dynamic-linked library (DLL) that manages and interacts with a specific data source. An OLE DB data source is any data file accessible through OLE DB. Although data sources 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. The following table shows examples of the most common OLE DB providers and data sources for SQL Server.

Note This release has been tested only against the Microsoft OLE DB Provider for SQL Server, Microsoft OLE DB Provider for Jet, Microsoft OLE DB Provider for Oracle, Microsoft OLE DB Provider for Indexing Service, and the Microsoft OLE DB Provider for ODBC. However, SQL Server distributed queries are designed to work with any OLE DB provider that implements the requisite OLE DB interfaces.

For a data source to return data through a linked server, the OLE DB provider (DLL) for that data source must be present on the same server as SQL Server.

Linked Server Details

This illustration shows the basics of how a linked server configuration functions.

Linked servers are typically used to handle distributed queries. When a client application executes a distributed query through a linked server, SQL Server breaks down the command and sends rowset requests to OLE DB. Rowsets are the central objects that enable all OLE DB data providers to expose data in tabular form. Conceptually, a rowset is a set of rows in which each row contains columns of data.

OLE DB then queries the provider for the rowset information. The provider, in turn, knows the specifics of calling the data source, opening the file, and returning the requested information. The rowset data is returned through the provider and OLE DB to SQL Server, where it is reconstructed and returned to the client application as a result set and set of output parameters (if applicable).

Managing a Linked Server Definition

When setting up a linked server, register the connection information and data source information with SQL Server. After registration is accomplished, that data source can always be referred to with a single logical name.

You can create or delete a linked server definition with stored procedures or through SQL Server Enterprise Manager.

• With stored procedures:
  • Create a linked server definition using sp_addlinkedserver. To view information about the linked servers defined in a given SQL Server, use sp_linkedservers. For more information, see sp_addlinkedserver and sp_linkedservers.
  • Delete a linked server definition using sp_dropserver. You can also use this stored procedure to remove a remote server. For more information, see sp_dropserver.
• With SQL Server Enterprise Manager:
  • Create a linked server definition using the SQL Server Enterprise Manager console tree and the Linked Servers node (under Security). Define the name, provider properties, server options, and security options for the linked server. For information about the various ways a linked server can be set up for different OLE DB data sources and the parameter values to be used, see sp_addlinkedserver.
  • Delete a linked server definition by right-clicking the linked server and clicking Delete.

Executing a Distributed Query

When executing a distributed query against a linked server, include a fully qualified, four-part table name for each data source to query. This four-part name should be in the form linked_server_name.catalog.schema.object_name. For example, to run a “SELECT * FROM employees” query against a SQL Server data file and an Oracle data file, the distributed query would look similar to the following:

SELECT emp.EmloyeeID, ord.OrderID, ord.Discount

FROM SQLServer1.Northwind.dbo.Employees AS emp,

     OracleSvr.Catalog1.SchemaX.Orders AS ord

WHERE ord.EmployeeID = emp.EmployeeID

      AND ord.Discount > 0

See Also