Welcome Sign in
SQL Server Developer Center
Click to Rate and Give Feedback
MSDN
MSDN Library
SQL Server
SQL Server 2008
Database Engine
Development
Distributed Queries
 Linked Server Considerations in a C...
Community Content
In this section
Statistics Annotations (0)
Other versions are also available for the following:
SQL Server 2008 Books Online (October 2008)
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, 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. (Use SQLNCLI and SQL Server will redirect to the latest version of SQL Server Native Client OLE DB Provider.)

sp_addlinkedserver @server = N'LinkServer',
    @srvproduct = N' ',
    @provider = N'SQLNCLI', 
    @datasrc = N'ServerNetName', 
    @catalog = N'AdventureWorks'
GO
SELECT *
FROM LinkServer.AdventureWorks.HumanResources.Employee
GO

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." This restriction does not apply when an INSERT...EXECUTE statement, issued by a connection that does not have multiple active result sets (MARS) enabled, executes against a loopback linked server. Note that the restriction still applies when MARS is enabled on a connection.

Tags What's this?: Add a tag
Community Content   What is Community Content?
Add new content RSS  Annotations
© 2008 Microsoft Corporation. All rights reserved. Terms of Use  |  Trademarks  |  Privacy Statement
Page view tracker