Distributed Queries on Multiple Instances of SQL Server

Distributed Queries on Multiple Instances
of SQL Server

Specifying an instance of Microsoft® SQL Server™ 2000 on a server running multiple instances of SQL Server requires no syntax changes to the Transact-SQL elements used in distributed queries. Instances can be specified in distributed queries using one of these methods:

• Specify a server name using the syntax 'server_name/instance_name' in the @datasrc parameter of sp_addlinkedserver.
  
  
• Specify Server=server_name; INSTANCENAME=instance_name in a connection string.

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

Examples of specifying a specific instance named Payroll on a server named London are:

-- Define a linked server on an instance of SQL Server using @datasrc.
sp_addlinkedserver
    @server = 'LondonPayroll1',
    @provider = 'SQLOLEDB',
    @datasource = 'London/Payroll'

-- Define a linked server on an instance of SQL Server using
-- INSTANCENAME in a provider string.
sp_addlinkedserver
    @server = 'LondonPayroll2',
    @provider = 'SQLOLEDB',
    @provstr = 'Server=London;INSTANCENAME=Payroll'

-- Specify an instance of SQL Server in OPENDATASOURCE
-- using Data Source.
SELECT *
FROM OPENDATASOURCE(
          'SQLOLEDB',
          'Data Source=London/Payroll;User ID=MyUID;Password=MyPass'
                   ).Northwind.dbo.Categories

-- Specify an instance of SQL Server in OPENROWSET
-- using a provider string.
SELECT a.*
FROM OPENROWSET(
          'SQLOLEDB',
          'Data Source=London;INSTANCENAME=Payroll;
           User ID=MyUID;Password=MyPass',
           Northwind.dbo.Categories
                ) AS a

-- Specify an instance of SQL Server in OPENROWSET
-- using a the datasource parameter.
SELECT a.*
FROM OPENROWSET(
          'SQLOLEDB','London/Payroll','MyUID','MyPass',
          'SELECT * FROM Northwind.dbo.Categories'
                ) AS a