Microsoft SQL Server 7.0 System Administration Training Kit
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
Introduction to Linked Servers
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
- Reference rowsets from the OLE DB data sources as tables in Transact-SQL statements sent to SQL Server. This means that client software does not need to use many different dialects of the SQL language and can access many different servers through a single connection to SQL Server.
- Reference multiple linked servers and perform either update or read operations against each individual linked server. A single distributed query can perform read operations against some linked servers and update operations against other linked servers. The types of queries executed against linked servers depend on the level of support for transactions present in the OLE DB providers.
Figure 6.5 illustrates how linked servers work.
Adding Linked Servers
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 |
NOTE
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.
Security Considerations for Linked Servers
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:
- The user is denied access.
- The user is mapped to a single login specified for all users that do not have a mapped login.
- SQL Server provides no login or password. This works for data sources that do not enforce security.
- SQL Server provides the user's SQL Server login credentials. This requires that the user have the same login name and password on the linked server, and it is called impersonation.
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.
System Stored Procedures for Working with Linked Servers
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 |
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 be queried. This four-part name should be in the form
| linked_server_name.catalog.schema.object_name |
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 |
- To configure a Microsoft Access linked server
- In the console tree, expand your server, expand Security, and then right-click Linked Servers. Click New Linked Server.
- In Linked Server, enter the name LINKEDJET for the new linked server.
- Under Server Type, click Other Data Source.
- For Provider Name, select Microsoft Jet 4.0 OLE DB Provider from the list of providers.
- In Data Source, enter the path to the linked server Microsoft Jet database C:\Sqladmin\Exercise\Ch06\Nwind.mdb .
- On the Security tab, click They Will Be Mapped To and type Admin in Remote User. This step maps all SQL Server logins to the login Admin, which is the default user name for Access databases that do not have security enabled.
- Click OK to close the Linked Server Properties dialog box and add the new linked server.
- In the console tree, expand LINKEDJET, and then click Tables. In the details pane you see a list of the tables from the Access Nwind.mdb file.
- Switch to or open Query Analyzer. Select Northwind in the DB list box.
- In the query pane, type and execute the following query:
| 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.
Lesson Summary
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.