Distributed Queries on Multiple Instances of SQL Server

Specifying an instance of SQL Server 2005 on a server running multiple instances of SQL Server 2005 requires no syntax changes to the Transact-SQL elements that are used in distributed queries. Instances can be specified in distributed queries by using one of the following methods:

• Specify a server name by using the syntax 'server_name\instance_name' in the @datasrc parameter of sp_addlinkedserver.
• Specify 'server=server_name\instance_name' in a connection string.

If an instance is not specified, the distributed query connects to the default instance of SQL Server 2005 on the specified server.

The following examples show specifying a specific instance named Payroll on a server named London.

-- Define a linked server on an instance of SQL Server by using @datasrc.
sp_addlinkedserver
    @server = 'LondonPayroll1',
    @srvproduct = ' ',
    @provider = 'SQLNCLI',
    @datasrc = 'London\Payroll'

-- Define a linked server on an instance of SQL Server by using
-- server_name\instance_name in a provider string.
sp_addlinkedserver
    @server = 'LondonPayroll2',
    @srvproduct = ' ',
    @provider = 'SQLNCLI',
    @provstr = 'Server=London\Payroll'

-- Specify an instance of SQL Server in OPENDATASOURCE
-- by using a provider string.
SELECT *
FROM OPENDATASOURCE('SQLNCLI',
    'Data Source=London\Payroll;Integrated Security=SSPI')
    .AdventureWorks.HumanResources.Employee

-- Specify an instance of SQL Server in OPENROWSET
-- by using a provider string.
SELECT a.*
FROM OPENROWSET(
     'SQLNCLI',
     'Server=London\Payroll;Trusted_Connection=yes',
      AdventureWorks.HumanResources.Employee) AS a

See Also

Concepts

Distributed Queries
SQL Native Client OLE DB Provider

Other Resources

sp_addlinkedserver (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance