Linked Servers

As mentioned before, you must first create a linked server before you can work with data from a remote SQL Server or another OLE DB datasource, such as Excel, Access, or Oracle. A linked server consists of an OLE DB or other datasource that is registered on the local SQL Server. After this is set up, Transact-SQL statements can be sent directly to a remote datasource via this linked server reference as if it were a normal relational table on SQL Server. This will be needed for both distributed queries and distributed transactions. For an extensive description of linked servers, see Chapter 19, "Managing Linked and Remote Servers."

Linked Server Setup to Remote Datasources

To enable you to execute Transact-SQL statements on a remote SQL Server or OLE DB datasource, you must create a link to the server or datasource. You can do so using either Enterprise Manager or the sp_addlinkedserver system-stored procedure:

 sp_addlinkedserver [@server =] 'server' 
[, [@srvproduct =] 'product_name'][, [@provider =] 'provider_name']
[, [@datasrc =] 'data_source'] [, [@location =] 'location']
[, [@provstr =] 'provider_string'] [, [@catalog =] 'catalog']

This defines a remote datasource as a linked server (like an Excel spreadsheet) and includes the OLE DB provider.

As you can see in the following code, not all of these parameters are required. You simply provide the ones needed for the particular type of OLE DB provider. Some parameters require less information than others to establish connectivity to that remote datasource. In the Customer Orders report example (Figure 32.5), you will need to set up a linked server called ExcelSW with sp_addlinkedserver as follows:

/* Set up of an Excel linked server */ 
EXEC sp_addlinkedserver
'ExcelSW',    /* linked server name you want to use*/
'Jet Excel',   /* product name ? can be anything */
'Microsoft.Jet.OLEDB.4.0', /* OLE provider name */
'd:\SWCustomers.xls',  /* datasource name */
NULL,  /* location not needed in this case */
'Excel 8.0',  /* Provider string if needed */
NULL    /* catalog name if needed */

To see the linked servers that have been defined on this SQL Server, simply use sp_linkedservers.

EXEC sp_linkedservers 

The sp_linkedservers execution provides the list of all linked servers on this SQL Server:

SRV_Name            SRV_Provider  SRV_Product SRV_Datasource etc.. 
C81124-C\DBARCH01          SQLOLEDB      SQL Server    C81124-C\DBARCH01
ExcelSW      Microsoft.Jet.OLEDB.4.0    Jet Excel d:\SWCustomers.xls   Excel 8.0
repl_distributor           SQLOLEDB      SQL Server    C81124-C\DBARCH01

As you can see, the list now contains the linked server that was just created (ExcelSW). However, you won't be able to use this linked server yet. A linked server login will have to be created for SQL Server to actually get to the datasource. You have essentially established the path to the datasource with sp_addlinkedserver.

To do this from Enterprise Manager, it is a simple one-step process from the linked server tree node. Navigate to the Security node and then right-click the Linked Servers Node (or choose the New Linked Server option from the Action menu pull-down list). Just provide the same information as is required for the type of OLE DB provider you want to access. As you can see from Figure 32.6, the General properties tab allows easy entry of a new linked server and a datasource file.

Figure 32.6. New Linked Server Properties specification.


Connecting to a Remote SQL Server

You do not need to specify the provider_name, data_source, location, provider_string, or catalog name when you are connecting to a SQL Server 6.5 (or higher) remote datasource. It is a simple process of providing the server name and the product name. As an example, you will create a linked server for another SQL Server named 'NWServer'.

sp_addlinkedserver 'NWServer', 'SQL Server' 

Establishing Linked Server Security

When a remote/distributed query is executed, the local SQL Server logs into the remote SQL Server or datasource on behalf of the user. Therefore, it might be necessary to establish security between the local and remote datasources. However, if the user's login ID and password exist on both the local and remote SQL servers, the local SQL Server can use the account information of the user to log into the remote SQL Server.

To establish login IDs and passwords between local and remote SQL Servers (or datasources), use the sp_addlinkedsrvlogin system-stored procedure.

Keep in mind that sp_addlinkedsrvlogin does not create user accounts. It merely maps a login account created on the local server to an account created on the remote server. In some cases, it utilizes a default system login placeholder (like with the Admin account with Excel spreadsheets). Again, for more detailed information on linked servers, refer to Chapter 19.

The syntax for the sp_addlinkedsrvlogin system-stored procedure is as follows:

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname' 
                           [ , [ @useself = ] 'useself' ]
                           [ , [ @locallogin = ] 'locallogin' ]
                           [ , [ @rmtuser = ] 'rmtuser' ]
                           [ , [ @rmtpassword = ] 'rmtpassword' ]

In the next example, you will set up the mapping required to use a SQL Server login ("sa" in this case) to access a linked server that is an Excel spreadsheet. Again, you will be setting up the linked server login for the ExcelSW linked server.

EXEC sp_addlinkedsrvlogin 
'ExcelSW',    /* remote/linked server name */
'false',             /* don't use user's own info */
'sa',         /* use already created login id */
'Admin',             /* maps to login id (for Excel) */
NULL                 /* no password, in this example */

Now the linked server reference is registered to SQL Server and the login/access has been mapped to enable it to be used.

You can also use a system-stored procedure to quickly find out what linked server logins have been defined. Simply execute the procedure without parameters to see all that have been defined on this local SQL Server:

EXEC sp_helplinkedsrvlogin 

The list of linked server logins are easily viewed as follows:

Linked Server       Local Login   Is Self Mapping   Remote Login 
C81124-C\DBARCH01   NULL          1                 NULL
ExcelNW             NULL          1                 NULL
ExcelNW             sa            0                 Admin
ExcelSW             NULL          1                 NULL
ExcelSW             sa            0                 Admin
NWServer            NULL          1                 NULL
repl_distributor    NULL          0                 distributor_admin

If you haven't yet added the login for the linked server that is a valid login at the referenced server, you will probably get the following error message on your first query attempt:

Server: Msg 7399, Level 16, State 1, Line 1 
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
Authentication failed.

SQL Server simply has nothing to map to on the linked/remote server side. You must resolve this by adding a valid user ID with the appropriate permissions for the linked server to use when attempting data access.

Keep in mind that when you complete the linking process, SQL Server really keeps these data resources linked in many ways. Most importantly, it keeps the schema definition linked. In other words, if the schema of a remote table on a linked server changes, any server that has links to it also knows the change. Even when the linked server's schema comes from something such as Excel, if you change the Excel spreadsheet in any way, that change will be automatically reflected back at the local SQL Server. This is extremely significant from a metadata (schema's) integrity point of view. This is what is meant by "completely linked!"

Querying a Linked Server

When you write a distributed query (or a distributed transaction for that matter), you must use a four-part name to refer to the linked objects. The linked server is said to conform to the IDBSchemaRowset interface. (It allows for the schema information to be retrieved from the remote server.) If the linked server doesn't conform to this interface, you will have to do all access using pass-through queries in the OPENDATASOURCE or OPENROWSET function. However, in most cases, you will be using this four-part name:


The name can be broken down as follows:

linked_server_name The unique network-wide name of the linked server [Servername\instancename|Servername]
catalog The catalog or database in the OLE DB that contains the object
schema The schema or object owner
object_name The name of the table or data object

For example, if you want to use the CustomersPlus table that is owned by the database owner (dbo) in the Southwind database on the C81124-C\DBARCH01 linked SQL Server, use the following four-part name to reference the CustomersPlus table:


Transact-SQL with Linked Servers

You can use the following Transact-SQL statements with linked servers:

  • SELECT statement with a WHERE clause or a JOIN clause

  • INSERT, UPDATE, and DELETE statements


Restrictions for use of Insert, Update, and Delete exist with certain OLE DB providers. Some OLE DB providers only allow reads, such as with flat files.

You cannot use the following:

  • CREATE, ALTER, or DROP statements

  • An ORDER BY clause in a SELECT statement if a large object column from a linked table is in the select list of the SELECT statement


Whenever possible, SQL Server pushes relational operations such as joins, restrictions, projections, sorts, and group by operations to the OLE DB datasource. SQL Server does not default to scanning the base table into SQL Server and performing the relational operations itself. SQL Server will actually query the OLE DB provider to determine the level of SQL grammar it supports, and, based on that information, will push as much as possible to the OLE DB provider.

To execute the original Customer Orders report-distributed query, you can execute the following SQL statement. As you will recall, the statement will need to join the Excel spreadsheet file (SWCustomers.xls) to the SQL Server table named CustomersPlus, resulting in this week's Customer Orders report for the sales director. The linked server and linked server login needed to fulfill this access have been set up. The following query generates the required report:

USE Southwind 
SELECT a.CustomerID, a.CompanyName, b.ContactName, b.Address,
b.City, b.Region, b.PostalCode, b.Country, b.Phone,
b.Fax, a.YTDBusiness
FROM CustomersPlus AS a
INNER JOIN [ExcelSW]...[SWCustomers$] AS b
ON a.CustomerID = b.CustomerID

The Customer Orders report now can generate a valid distributed resultset as follows:

CustomerID   CompanyName              ContactName 
        Address              City         Region        . . . YTDBusiness
BLAUS               Blauer See Delikatessen    Vilay Sithongkang
        4394 Water Bridge     Concord      CA            3239.8000
CHOPS               Chop-suey Chinese          Martin Sommer
        6 of One Half a St.    Berkeley    CA           12886.3000
PICCO               Piccolo und mehr           Adam Greifer
121 All the way up Ct. Los Angeles CA           26259.9500

An alternative method of executing a distributed SQL Query is to use the OPENQUERY syntax. Following is an example of a remote/distributed query against the ExcelSW linked server:

SELECT CustomerID, Address 
FROM OPENQUERY([ExcelSW], 'SELECT CustomerID, Address
  FROM [SWCustomers$]')
Executing a Stored Procedure Via a Linked Server

It is possible to execute a stored procedure via a linked server. The server hosting the client connection will accept the client's request and send it to the linked server. The EXECUTE statement must contain the name of the linked server as part of its syntax:

EXECUTE servername.dbname.owner.procedure_name 

This example executes sp_helpsrvrole, which shows a list of available fixed server roles on the 'NWServer' remote server:

EXEC NWServer.master.dbo.sp_helpsrvrole 

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features