SQL Server allows you to create links to OLE DB data sources called linked servers. This section explores the creation of linked servers and their use in queries.
After this lesson, you will be able to
- Add a linked server
- Execute a distributed query
Estimated lesson time: 50 minutes
SQL Server allows you to create links to OLE DB data sources called linked servers. This allows SQL Server clients to perform fully distributed queries and transactions. After linking to an OLE DB data source, you can
Figure 6.5 illustrates how linked servers work.
A linked server definition specifies an OLE DB provider and an OLE DB data source.
An OLE DB provider is a dynamic-link library (DLL) that manages and interacts with a specific data source. An OLE DB data source is any data store accessible through OLE DB. Although data sources queried through linked server definitions are usually database servers (such as SQL Server or Oracle), OLE DB providers exist for a wide variety of files and file formats, including file-based databases (such as Microsoft Access and Microsoft Visual FoxPro), 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.
Figure 6.5 How linked servers work
|OLE DB provider||OLE DB data source|
|Microsoft OLE DB Provider for SQL Server||SQL Server database, such as pubs or Northwind|
|Microsoft OLE DB Provider for Jet||Pathname of .MDB database file|
|Microsoft OLE DB Provider for ODBC||ODBC data source name (pointing to a particular database)|
|Microsoft OLE DB Provider for Oracle||SQL*Net alias that points to an Oracle database|
|Microsoft OLE DB Provider for Indexing Service||Content files on which property searches or full-text searches can be run|
Linked server support has been tested with 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 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.
When setting up a linked server, register the connection information and data source information with SQL Server. After registration is accomplished, the data source can always be referred to with a single logical name.
You can create or delete a linked server definition with system stored procedures or through SQL Server Enterprise Manager.
For a table of the different parameter values that you need to specify when creating a linked server, see sp_addlinkedserver in Books Online.
When you execute a query against a linked server, SQL Server must provide a login name and password to the linked server on behalf of the user executing the query.
The login name and password provided to the linked server can be specified explicitly by adding a mapped login for the linked server. If many users need to use the linked server, it may not be practical to added mapped logins for each user. If a mapped login has not been created for a user who is trying to use a linked server, one of the following can occur:
When creating mapped logins for users, you can either specify a login name and password to be used on the linked server or specify that the user be impersonated on the linked server. Login mappings are stored on SQL Server, which passes the relevant login information to the linked server whenever necessary.
By specifying that users without login mappings must be denied access, you can control access to other data sources at the SQL Server level or provide access control to data sources that do not provide their own security. For example, you could place a Microsoft Access database file on a Windows NT drive and use NTFS permissions to disallow access to all users. Only users that have SQL Server logins would gain access to the Access database as a linked server.
SQL Server provides system-stored procedures for working with linked servers. For example, the sp_addlinkedserver system stored procedure is used to create a linked server definition, and the sp_linkedservers system stored procedure is used to view information about linked servers. The following table provides a list of system stored procedures that can be used for working with linked servers.
|System stored procedure||Purpose|
|sp_addlinkedserver||Create a linked server definition|
|sp_linkedservers||View information about linked servers|
|sp_dropserver||Delete a linked server definition|
|sp_addlinkedsrvlogin||Add a linked server login mapping|
|sp_droplinkedsrvlogin||Delete a linked server login mapping|
When executing a distributed query against a linked server, include a fully qualified, four-part table name for each data source to be queried. This four-part name should be in the form
On SQL Server, catalog refers to the database name, and schema refers to the table owner. The following example shows a query that retrieves data from linked SQL Server and Oracle databases:
SELECT emp.EmloyeeID, ord.OrderID, ord.Discount FROM SQLServer1.Northwind.dbo.Employees emp INNER JOIN OracleSvr.Catalog1.SchemaX.Orders ord ON ord.EmployeeID = emp.EmployeeID WHERE ord.Discount > 0
SELECT ProductName, CategoryName, DATALENGTH(ProductName) FROM LINKEDJET...Products Prd JOIN Categories Cat ON Prd.CategoryID = Cat.CategoryID
This query retrieves data from the Category table in the SQL Server Northwind database and joins it to data retrieved from the Products table in the Access Nwind.mdb database file.
The JOIN syntax of this query is valid in Transact-SQL but will not work in Microsoft Access (INNER JOIN would have to be specified). The query uses the Transact-SQL DATALENGTH function, which is not available in Microsoft Access. The DATALENGTH function returns the length of the data in the specified column. The value returned is twice the number of characters in the column because the data is stored using two-byte-per-character Unicode characters.
A linked server allows access to distributed, heterogeneous queries against OLE DB data sources. For example, information can be accessed from an Oracle or Access database by using a SQL Server connection. SQL Server provides system-stored procedures to create and view information about linked servers. Once the linked server is created, a query can be run that uses both SQL Server tables and other OLE DB data sources.