Identifying a Data Source Using a Linked Server Name

A linked server is a virtual server that has been defined to Microsoft® SQL Server™ with all the information needed to access an OLE DB data source. After a linked server is defined, a four-part name in the form linked_server_name.catalog.schema.object_name can be used in Transact-SQL statements to reference data objects in that linked server. The table describes the parts of a four-part name.

Part name Description
linked_server Linked server referencing the OLE DB data source
catalog Catalog in the OLE DB data source that contains the object
schema Schema in the catalog that contains the object
object_name Data object in the schema

SQL Server uses the linked server name to identify the OLE DB provider and the data source. The catalog, schema, and object_name parameters are passed to the OLE DB provider to identify a specific data object. When the linked server refers to a server running SQL Server, catalog refers to a database and schema refers to an owner ID.

This illustration shows how a four-part SQL Server name resolves to an object in the OLE DB provider.

This example defines a linked server against a server running SQL Server and references one of the remote tables using a four-part name in a SELECT statement.

sp_addlinkedserver @server = N'LinkServer',

    @srvproduct = N' ',

    @provider = N'SQLOLEDB',

    @datasrc = N'ServerNetName',

    @catalog = N'Northwind'

GO

SELECT *

FROM LinkServer.Northwind.dbo.Shippers

GO

  

Always use fully qualified names when working with objects on linked servers. The OLE DB specification does not define implicit name resolution; it only allows the passing of a NULL. When NULL is passed as the schema name, OLE DB further specifies that no separator character can be supplied. Because of this, a query without a schema name generates a 7314 error, even when the linked server is another installation of SQL Server:

SELECT *

FROM LinkServer.Northwind..Shippers

  

Msg 7314, Level 16, State 1

[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider 'SQLOLEDB' does not contain table "Northwind"."Shipppers".

  

The error message refers to Northwind.Shippers because the second period was dropped due to the OLE DB rule about dropping the separator when schema name is NULL.

Loopback Linked Servers

Linked servers can be defined to point back to the server on which they are defined. For example, executing this sp_addlinkedserver statement on a server named MyServer defines a loopback linked server:

sp_addlinkedserver @server = N'MyLink',

    @srvproduct = N' ',

    @provider = N'SQLOLEDB',

    @datasrc = N'MyServer',

    @catalog = N'Northwind'

GO

  

Transact-SQL statements that use MyLink as the server name loop out through the SQLOLEDB provider and back into the local server. Loopback servers are most useful when testing an application that uses distributed queries on a single server network.

Loopback linked servers cannot be used in a distributed transaction. Attempting a distributed query against a loopback linked server from within a distributed transaction raises an error:

Msg: 3910 Level: 16 State: 1

[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by another session.

  

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.