What is a Linked Server?

Think of a Linked Server as an alias on your local SQL server that points to an external data source. This external data source can be Access, Oracle, Excel or almost any other data system that can be accessed by OLE or ODBC--including other MS SQL servers. An MS SQL linked server is similar to the MS Access feature of creating a "Link Table."

Why use a Linked Server?

With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data.

While it would be convenient to have all of our business data in one place, there are too many obstacles such as Vendor applications built for a specific data store, data sets too large for one server, legacy flat file applications that are cost prohibitive to recreate and changing business standards, preventing this from happening.

"Replication Manager" has made moving data from one SQL Server to another on a regular basis relatively easy. However, duplicating data to an application server is not always the best solution. If your source is large, and you cannot predict what subset of data you will need, then a linked server may be a better solution.

If you have a very large data set, there may be performance benefits to splitting your data into pieces, and moving those pieces onto different servers. Then using distributed partitioned views to present the data as one source. If so, linked servers are the technology that makes it possible.

Why not use a Linked Server?

If the remote data is not yours, and the owning department will not allow you remote access, then a linked server is out. You will have to rely on some type of scheduled pickup and exchange.

When absolute, best possible performance is required, local data will out perform a linked server.

If the physical link between your SQL Server and the remote data is slow, or not reliable, then a linked server is not a good solution.

History

Linked servers are a superset of "remote servers." Remote servers allowed the running of stored procedures on distributed SQL Server machines. SQL 2000 BOL states, "Support for remote servers is provided for backward compatibility only. New applications that must execute stored procedures against remote instances of SQL Server should use linked servers instead." Support for remote servers may be discontinued in the future. In addition, remote servers only allowed stored procedures to be run. Linked servers allow both stored procedures and ad hoc queries.

Distributed Transaction Coordinator (DTC)

Before starting the examples, we need to start the Distributed Transaction Coordinator. The DTC manages the committing of transactions when there are several different data sources involved. For Windows 2000, service pack 1 is required.

+ Open the services MMC, locate and start the Distributed Transaction Coordinator using the default settings.

SQL to Excel

In our first example, we will link an Excel sheet to an MS SQL server. The Excel example is a one-sheet copy of the SQL Pubs table Authors.

Right-click to download the Excel Sheet

+ Open the SQL Enterprise Manager and navigate to Security/Linked Servers. Underneath, (No items) should be visible.

+ Right click the Linked Server icon and select New Linked Server.

+ Create a new linked server by entering any name in the Linked Server text box. Select "Other" as the data source, selecting the "Microsoft Jet 4" provider. The product is "Excel." The data source is the file path name. The Provider string is "Excel 8.0" for Excel versions 97, 2000, and 2002. Then click OK to finish.

Now in Enterprise Manager, under Linked Servers, the new "Excel_AUTHORS" server should appear with a table for each Excel Sheet.

To verify your connection, open SQL Query Analyzer and enter SELECT * FROM EXCEL_AUTHORS...Sheet1$. (3 dots between EXCEL_AUTHORS and Sheet1$) The author table should be returned.

Four-Part Naming

This type of query on a linked server access is called a "Direct Reference." We are directly referencing the object (Sheet1$ in this case) in our query, as opposed to using a function to help setup the connection, or executing a stored procedure located on the linked server. Direct Referencing only works when the OLE DB provider supports Four-Part Naming. The naming parts are Link Name, Database Name, Owner and Object. We will be exploring more on four-part naming when we start connecting to traditional databases as linked servers. For additional information on Four-Part Naming, see BOL Four-Part Naming or IDBSchemaRowset. IDBSchemaRowset is the meta data handler for linked servers.

Back to Excel

The first row of the Excel sheet has automatically been used as Column headings for our table, allowing us to query the Excel sheet in standard TSQL statements such as: SELECT address FROM EXCEL_AUTHORS...Sheet1$.

The usual DML (Data Manipulation Language) commands are also supported. Such as:

INSERT INTO EXCEL_AUTHORS...Sheet1$ 

	(address, au_fname)

VALUES

	(' 111 State St', 'Don')

This next return will verify our insert succeeded.

sp_addlinkedserver

Using the system stored procedure sp_addlinkedserver instead of Enterprise Manager can also create the linked server. Following is the sp syntax:

sp_addlinkedserver [ @server = ] 'server' 

    [ , [ @srvproduct = ] 'product_name' ] 

    [ , [ @provider = ] 'provider_name' ] 

    [ , [ @datasrc = ] 'data_source' ] 

    [ , [ @location = ] 'location' ] 

    [ , [ @provstr = ] 'provider_string' ] 

    [ , [ @catalog = ] 'catalog' ]

To repeat the Excel example used previously, use the following parameters:

EXEC sp_addlinkedserver 'EXCEL2',

	'Excel',

	'Microsoft.Jet.OLEDB.4.0',

	'c:\temp\authors.xls',

	NULL,

	'Excel 8.0',

	NULL

The location and catalog parameters are not used in our example. A select will verify that our link was successful.

Conclusion

Linked Servers are a great way to include outside data sources into your TSQL. There are providers for most desktop applications. In future articles, we will begin using security, passwords, additional Query Analyzer commands and connecting to SQL, Access and Oracle databases.

» See All Articles by Columnist Don Schlichting