Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Distributed Queries on Multiple Instances of SQL Server

Specifying an instance of SQL Server on a server running multiple instances of SQL Server 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 on the specified server.

The following examples show specifying a specific instance named Payroll on a server named London. (Use SQLNCLI and SQL Server will redirect to the latest version of SQL Server Native Client OLE DB Provider.)

-- 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

Community Additions

ADD
Show:
© 2015 Microsoft