Linked Server Considerations in a Clustered SQL Server
When linked servers are configured in a clustered SQL Server against OLE DB providers that are not shipped with SQL Server 2005, make sure that the OLE DB providers are installed in all nodes of the cluster. Also, any properties that define the linked server should be location transparent; they should not contain information that assumes SQL Server is always running on a specific node of the cluster.
The following 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'SQLNCLI',
@datasrc = N'ServerNetName',
@catalog = N'AdventureWorks'
GO
SELECT *
FROM LinkServer.AdventureWorks.HumanResources.Employee
GO
Loopback Linked Servers
Linked servers can be defined to point back (loop back) to the server on which they are defined. Loopback servers are most useful when testing an application that uses distributed queries on a single server network.
For example, executing the following sp_addlinkedserver stored procedure on a server named MyServer defines a loopback linked server:
sp_addlinkedserver @server = N'MyLink',
@srvproduct = N' ',
@provider = N'SQLNCLI',
@datasrc = N'MyServer',
@catalog = N'AdventureWorks'
GO
Transact-SQL statements that use MyLink as the server name loop through the SQLNCLI provider and back to the local server.
Loopback linked servers cannot be used in a distributed transaction. Trying a distributed query against a loopback linked server from within a distributed transaction causes an error, such as error 3910: "[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by another session."
See Also
Concepts
SQL Native Client OLE DB Provider
Distributed Queries
Other Resources
sp_addlinkedserver (Transact-SQL)