How do I… Query foreign data using SQL Server’s linked servers?

This information is also available as a PDF download.

If you have a magic wand or a special relationship with some data fairy, all of your data is in the same database on the same server. Most of us aren’t that lucky. Sometimes, the data we need is a non-database format or resides on a different server. SQL Server is flexible enough to offer a number of ways to get the data you need. One of the easiest methods is to directly link to the data via a linked server. (Microsoft SQL Server 2005 Express Edition also supports linked servers.)

What’s a linked server?

A linked server is simply a connection to an Object Linking and Embedding Database (OLEDB) data source. Technically, OLEDB is a Microsoft standard API for retrieving data from a wide variety of data. If that’s clear as mud, don’t worry. The good news is that it’s flexible enough to link to database and non-database formats, such as a spreadsheet or e-mail client. Simply put, SQL Server supports any OLEDB provider (also called a driver). There’s more good news: You can use Transact-SQL or Management Studio to make the connection. After you create a linked server, SQL Server can log into another database server. That means you can run queries on a remote server. You have two types of linked server queries at your disposal: ad hoc and permanent.

Ad hoc links

Technically, you won’t use the term linked server to identify an ad hoc query. That term really refers to a SQL Server object. However, you will often see the term used to refer to an ad hoc linked query. An ad hoc query opens and closes the connection. A permanent linked server is always available. Use OPENROWSET for infrequent linked tasks, using the following syntax:

OPENROWSET('providername', 'datasource', 'username', 'password', object)

An OPENROWSET link consumes less space in your database. Use valid Transact-SQL statements to manipulate the retrieved data. The arguments are self-explanatory, but remember that datasource is the source’s full path, not just a filename. In addition, the provider provides the instructions that SQL Server needs to get in and grab data. They’re specific to the foreign software you’re accessing.

Now, let’s look at a simple ad hock query that selects all the records from the Employees table in the Microsoft Access sample database, Northwind:

SELECT *

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'C:Program FilesMicrosoft OfficeOffice11Office11SamplesNorthwind.mdb';

'admin';'', Employees)

AS EmployeesFromAccess

Figure A shows the quickly retrieved results, which depend on the user (admin) having the appropriate permissions. (If you want to run this query, be sure to update the path to Northwind.mdb to accommodate your system.) The provider string is specific to the data engine, Jet. The AS keyword provides a name for the new table inside SQL Server.

Figure A: Use OPENROWSET for infrequent queries of foreign data (Express edition).

If the ad hoc query returns an error, you probably need to enable the ad hoc query feature. (SQL Server disables it by default.) To enable ad hoc queries, run the SQL Server Configuration Manager (the SQL Server Surface Area Configuration utility in the Express edition). Click the Surface Area Configuration For Features link and check the Enable OPENROWSET And OPENDATASOURCE Support option. Then, click OK and close the utility. Alternatively, run the sp_configure stored procedure.

Permanent links

SQL Server’s linked server object creates a permanent link to a remote server. When the user logs in, SQL Server also logs into the remote server. The first step to retrieving foreign data via a linked server is to let SQL Server know that you plan to talk to another source (server). To do so, execute SQL Server’s sp_addlinkedserver stored procedure, using the following syntax:

sp_addlinkedserver server, productname, provider, datasource, location, providerstring, catalog

Refer to Table A for an explanation of each of the procedure’s arguments. Notice that a few of the arguments are limited to code.

Table A: sp_addlinkedserver stored procedure arguments

The arguments aren’t optional, but you won’t use them all together, which explains the NULL value defaults. The provider determines which arguments you need. When using SQL Server, you need only the first two arguments.

Now, let’s suppose you want to create a permanent link to another SQL Server database on the Human Resources server. To do so, you’d use the following statement:

EXEC sp_addlinkedserver @server = 'EmployeeStats',

@provider = 'SQL Server',

@datasrc = 'Human Resources'

The linked server’s name, EmployeeStats, is a string that you provide to represent the server. It doesn’t exist until you name and create it. The following syntax statements represent the creation of a linked server to several common data sources:

Microsoft Access

EXEC sp_addlinkedserver @server = 'NorthwindDemo',

@srvproduct = 'Microsoft.Jet.OLEDB.4.0',

@provider = 'Microsoft.Jet.OLEDB.4.0',

@datasrc = 'C:LinkedNorthwind.mdb'

Microsoft Excel

EXEC sp_addlinkedserver @server = 'ExcelEmployeeData',

@srvproduct = 'Microsoft.Jet.OLEDB.4.0',

@provider = 'Microsoft.Jet.OLEDB.4.0',

@datasrc = 'C:LinkedEmployees.xls'

Oracle

EXEC sp_addlinkedserver @server = 'OracleDemo',

@srvproduct = 'Oracle',

@provider = 'MSDAORA',

@datasrc = 'OracleAlias'

Oracle is a bit different because you must create an alias that points to the Oracle server rather than specify an actual file (@datasrc). Every situation is different. Your best defense is experience and an informed technical staff.

SQL Server throws a monkey wrench into the works because it doesn’t return an error if you use the wrong syntax. Instead, any attempt to use the linked server simply fails, but the failure might not be obvious. My best recommendation is to test a query that you know should return data. If you get nothing, you know the linked server isn’t really connecting and the problem is most likely the arguments you fed sp_addlinkedserver to create the linked server.

Using Management Studio

Using Management Studio to create a linked server is even easier. Look for the Linked Server folder in the Server Objects or Security folder (depending on which version you’re using). The good news is that Management Studio simplifies the process. You still have to enter the product name, provider, and path to the file. If an option is disabled and you need it, as shown in Figure B, you must turn to sp_addlinkedserver. In this case, Access doesn’t support the Location and Catalog options, so Management Studio disables them.

Figure B: Management Studio doesn’t support all of the options for every provider (Express edition).

Retrieving data

Once the linked server exists, you can use it to query data, just as if you were working with SQL Server tables. Figure C shows the result of retrieving all the Employee records from Northwind using the SQL statement via a linked server named NorthwindDemo:

SELECT * FROM NorthwindDemo...Employees

The statement identifies the remote server and the table from which you’re retrieving data.

Figure C: Retrieve Access data via a linked server (Express edition).

When querying data via the ExcelData linked server, include a named range as follows:

SELECT *

FROM ExcelData, Employees

One or two gotchas

The biggest catch to all this simplicity is, as always, security. If you’re working with Windows Authentication, the system accommodates well. If the user has the appropriate permissions for the servers and data sources, linked queries will work.

If you have users outside the Windows Authentication environment, you can use a linked login. Create a standard login and assign the appropriate permissions on the remote server. Then, from the sending server, use sp_addlinkedsrvlogin to map local logins to the remote logins using the following syntax:

sp_addlinkedsrvlogin remoteservername, useseif, locallogin, remoteuser, remotepassword

For instance, after executing the following statement, the user logging in locally as Susan1 with the password rabbitX will have access to data via the NorthwindDemo linked server if you’ve correctly mapped the logins:

EXEC sp_addlinkedsrvlogin @rmtsrvname = 'NorthwindDemo',

@useseif = FALSE,

@locallogin = 'NULL',

@rmtuser = 'Susan1',

@rmtpassword = 'rabbitX'

Table B describes the sp_addlinkedsrvlogin arguments.

Table B: sp_addlinkedsrvlogin arguments

Another catch is the ever-growing, ever-changing list of providers and connection strings that you’ll need. You’ll find a comprehensive list of provider strings at ConnectionStrings.com.

Provider names aren’t intuitive either. Table C lists some common provider names.

Table C: Provider names

Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is Mastering Microsoft SQL Server 2005 Express, with Mike Gunderloy, published by Sybex. Other collaborations with Gunderloy are Automating Microsoft Access 2003 with VBA, Upgrader’s Guide to Microsoft Office System 2003, ICDL Exam Cram 2, and Absolute Beginner’s Guide to Microsoft Access 2003, all published by Que. Currently, Susan volunteers as the Publications Director for Database Advisors. You can reach her at ssharkins@setel.com.

Daily Tech Insider Newsletter

Stay up to date on the latest in technology with Daily Tech Insider. We bring you news on industry-leading companies, products, and people, as well as highlighted articles, downloads, and top resources. You’ll receive primers on hot tech topics that will help you stay ahead of the game.

Delivered Weekdays

Sign up today