建立一個連結的伺服器 – sp_addlinkedserver

sp_addlinkedserver
建立一個連結的伺服器，使其允許對分布式的、針對 OLE DB 資料來源的異類查詢進行訪問。在使用 sp_addlinkedserver 建立連結的伺服器之後，此伺服器就可以執行分散式查詢。如果連結的伺服器定義為 Microsoft? SQL Server?，則可執行遠端預存程序。

文法
sp_addlinkedserver [ @server = ] 'server'
    [ , [ @srvproduct = ] 'product_name' ]
    [ , [ @provider = ] 'provider_name' ]
    [ , [ @datasrc = ] 'data_source' ]
    [ , [ @location = ] 'location' ]
    [ , [ @provstr = ] 'provider_string' ]
    [ , [ @catalog = ] 'catalog' ]

參數
[ @server = ] 'server'

要建立的連結的伺服器的本地名稱，server 的資料類型為 sysname，沒有預設設定。

如果有多個 SQL Server 執行個體，server 可以為 servername/instancename。此連結的伺服器可能會被引用為下面樣本的資料來源：

SELECT *FROM    [servername/instancename.]pubs.dbo.authors.

如果未指定 data_source，則伺服器為該執行個體的實際名稱。

[ @srvproduct = ] 'product_name'

要添加為連結的伺服器的 OLE DB 資料來源的產品名稱。product_name 的資料類型為 nvarchar(128)，預設設定為 NULL。如果是 SQL Server，則不需要指定 provider_name、data_source、location、provider_string 以及目錄。

[ @provider = ] 'provider_name'

與此資料來源相對應的 OLE DB 提供者的唯一程式識別碼 (PROGID)。provider_name 對於安裝在當前電腦上指定的 OLE DB 提供者必須是唯一的。provider_name 的資料類型為nvarchar(128)，預設設定為 NULL。OLE DB 提供者應該用給定的 PROGID 在註冊表中註冊。

[ @datasrc = ] 'data_source'

由 OLE DB 提供者解釋的資料來源名稱。data_source 的資料類型為 nvarchar(4000)，預設設定為 NULL。data_source 被當作 DBPROP_INIT_DATASOURCE 屬性傳遞以便初始化 OLE DB 提供者。

當連結的伺服器針對於 SQL Server OLE DB 提供者建立時，可以按照 servername/instancename 的形式指定 data_source，它可以用來串連到運行於特定電腦上的 SQL Server 的特定執行個體上。servername 是運行 SQL Server 的電腦名稱，instancename 是使用者將被串連到的特定 SQL Server 執行個體的名稱。

[ @location = ] 'location'

OLE DB 提供者所解釋的資料庫的位置。location 的資料類型為 nvarchar(4000)，預設設定為 NULL。location 作為 DBPROP_INIT_LOCATION 屬性傳遞以便初始化 OLE DB 提供者。

[ @provstr = ] 'provider_string'

OLE DB 提供者特定的連接字串，它可標識唯一的資料來源。provider_string 的資料類型為 nvarchar(4000)，預設設定為 NULL。Provstr 作為 DBPROP_INIT_PROVIDERSTRING 屬性傳遞以便初始化 OLE DB 提供者。

當針對 Server OLE DB 提供者提供了連結的伺服器後，可將 SERVER 關鍵字用作 SERVER=servername/instancename 來指定執行個體，以指定特定的 SQL Server 執行個體。servername 是 SQL Server 在其上啟動並執行電腦名稱，instancename 是使用者串連到的特定的 SQL Server 執行個體名稱。

[ @catalog = ] 'catalog'

建立 OLE DB 提供者的串連時所使用的目錄。catalog 的資料類型為sysname，預設設定為 NULL。catalog 作為 DBPROP_INIT_CATALOG 屬性傳遞以便初始化 OLE DB 提供者。

傳回碼值
0（成功）或 1（失敗）

結果集
如果沒有指定參數，則 sp_addlinkedserver 返回此訊息：

Procedure 'sp_addlinkedserver' expects parameter '@server', which was not supplied.

使用適當 OLE DB 提供者和參數的 sp_addlinkedserver 返回此訊息：

Server added.

注釋
下表顯示為可通過 OLE DB 訪問的資料來源設定連結的伺服器的方法。對於給定的資料來源，可以使用多種方法為其設定連結的伺服器，下表中可能有不止一行適用於一種資料來源類型。下表也顯示了用於設定連結的伺服器的 sp_addlinkedserver 參數值。

遠程 OLE DB 資料來源
OLE DB
提供者
product_name
provider_name
data_source

location
provider_string

catalog
SQL Server 用於 SQL Server 的 Microsoft OLE DB 提供者 SQL Server (1)（預設值） - - - - -
SQL Server 用於 SQL Server 的 Microsoft OLE DB 提供者 SQL Server SQLOLEDB SQL Server 的網路名稱（用於預設執行個體） - - 資料庫名稱（可選）
SQL Server 用於 SQL Server 的 Microsoft OLE DB 提供者 - SQLOLEDB 伺服器名/執行個體名（對於特定執行個體） - - 資料庫名稱（可選）
Oracle 用於 Oracle 的 Microsoft OLE DB 提供者 任何 (2)  MSDAORA 用於 Oracle 資料庫的 SQL*Net 別名
 - - -
Access/
Jet 用於 Jet 的 Microsoft OLE DB 提供者 任何 Microsoft.Jet.OLEDB.4.0 Jet 資料庫檔案的完整路徑名 - - -
ODBC 資料來源 用於 ODBC 的 Microsoft OLE DB 提供者 任何 MSDASQL ODBC 資料來源的系統 DSN - - -
ODBC 資料來源 用於 ODBC 的 Microsoft OLE DB 提供者 任何 MSDASQL - - ODBC 連接字串 -
檔案系統 用於索引服務的 Microsoft OLE DB 提供者 任何 MSIDXS 索引服務類別目錄名稱 - - -
Microsoft Excel 試算表 用於 Jet 的 Microsoft OLE DB 提供者 任何 Microsoft.Jet.OLEDB.4.0 Excel 檔案的完整路徑名 - Excel 5.0 -
IBM DB2 資料庫 用於 DB2 的Microsoft OLE DB 提供者 任何 DB2OLEDB - - 請參見用於 DB2 文檔的 Microsoft OLE DB 提供者 DB2 資料庫的目錄名

(1 ) 這種設定連結的伺服器的方式強制連結的伺服器的名稱與遠程 SQL Server 的網路名稱相同。使用 server 指定伺服器。
(2 ) "任何"指產品名稱可以任意。

data_source、location、provider_string 和 catalog 參數標識連結的伺服器指向的資料庫。如果任一參數為 NULL 值，則不設定相應的 OLE DB 初始化屬性。

 

說明  若要在 SQL Server 6.x 版上使用 SQL Server 2000 版的 Microsoft OLE DB 提供者，請在 6.x 版 SQL Server 上運行 /Microsoft SQL Server/Install/Instcat.sql 指令碼。此指令碼對於在 SQL Server 6.x 伺服器上運行分散式查詢是基本的。

在群集環境中，當指定指向 OLE DB 資料來源的檔案名稱時，應使用通用命名規則 (UNC) 名稱或共用磁碟機指定位置。

許可權
執行許可許可權預設授予 sysadmin 和 setupadmin 固定伺服器角色的成員。

樣本
A. 使用用於 SQL Server 的 Microsoft OLE DB 提供者
使用用於 SQL Server 的 OLE DB 建立連結的伺服器
下面的樣本建立一台名為 SEATTLESales 的連結的伺服器，該伺服器使用用於 SQL Server 的 Microsoft OLE DB 提供者。

USE master
GO
EXEC sp_addlinkedserver
    'SEATTLESales',
    N'SQL Server'
GO

在 SQL Server 的執行個體上建立連結的伺服器
此樣本在 SQL Server 的執行個體上建立一台名為 S1_instance1 的連結的伺服器，該伺服器使用 SQL Server 的 Microsoft OLE DB 提供者。

EXEC    sp_addlinkedserver    @server='S1_instance1', @srvproduct='',
                                @provider='SQLOLEDB', @datasrc='S1/instance1'

B. 使用用於 Jet 的 Microsoft OLE DB 提供者
此樣本建立一台名為 SEATTLE Mktg 的連結的伺服器。

 

說明  本樣本假設已經安裝 Microsoft Access 和樣本 Northwind 資料庫，且 Northwind 資料庫駐留在 C:/Msoffice/Access/Samples。

USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
   @server = 'SEATTLE Mktg',
   @provider = 'Microsoft.Jet.OLEDB.4.0',
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = 'C:/MSOffice/Access/Samples/Northwind.mdb'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver
   'SEATTLE Mktg',
   'OLE DB Provider for Jet',
   'Microsoft.Jet.OLEDB.4.0',
   'C:/MSOffice/Access/Samples/Northwind.mdb'
GO

C. 使用用於 Oracle 的 Microsoft OLE DB 提供者
此樣本建立一台名為 LONDON Mktg 的連結的伺服器，該伺服器使用用於 Oracle 的 Microsoft OLE DB 提供者，並且假設此 Oracle 資料庫的 SQL*Net 別名為 MyServer。

USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
   @server = 'LONDON Mktg',
   @srvproduct = 'Oracle',
   @provider = 'MSDAORA',
   @datasrc = 'MyServer'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver
   'LONDON Mktg',
   'Oracle',
   'MSDAORA',
   'MyServer'
GO

D. 將 data_source 參數與用於 ODBC 的 Microsoft OLE DB 提供者一起使用
此樣本建立一台名為 SEATTLE Payroll 的連結的伺服器，該伺服器使用用於 ODBC 的 Microsoft OLE DB 提供者和 data_source 參數。

 

說明  在執行 sp_addlinkedserver 之前，必須在伺服器上將指定的 ODBC 資料來源名稱定義為系統 DSN。

USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
   @server = 'SEATTLE Payroll',
   @provider = 'MSDASQL',
   @datasrc = 'LocalServer'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver
   'SEATTLE Payroll',
   '',
   'MSDASQL',
   'LocalServer'
GO

E. 將 provider_string 參數與用於 ODBC 的 Microsoft OLE DB 提供者一起使用
此樣本建立一台名為 LONDON Payroll 的連結的伺服器，該伺服器使用用於 ODBC 的 Microsoft OLE DB 提供者和 provider_string 參數。

 

說明  有關 ODBC 連接字串的更多資訊，請參見 SQLDriverConnect 和如何分配控制代碼並與 SQL Server (ODBC) 串連。

USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
   @server = 'LONDON Payroll',
   @provider = 'MSDASQL',
   @provstr = 'DRIVER={SQL Server};SERVER=MyServer;UID=sa;PWD=;'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver
   'LONDON Payroll',
   '',
   'MSDASQL',
   NULL,
   NULL,
   'DRIVER={SQL Server};SERVER=MyServer;UID=sa;PWD=;'
GO

F. 在 Excel 試算表上使用用於 Jet 的 Microsoft OLE DB 提供者
若要建立使用用於 Jet 的 Microsoft OLE DB 提供者以訪問 Excel 試算表的連結的伺服器定義，請首先在 Excel 中建立一個命名的範圍以指定要在 Excel 工作表中選擇的行和列。然後，可將此範圍的名稱引用為分散式查詢中的表名稱。

EXEC sp_addlinkedserver 'ExcelSource',
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'c:/MyData/DistExcl.xls',
   NULL,
   'Excel 5.0'
GO

為了訪問 Excel 試算表中的資料，請將某個範圍內的單元與某個名稱相關聯。通過將範圍的名稱用作表名稱，可以訪問指定的已命名範圍。下列查詢利用前面設定的連結的伺服器，可訪問稱為 SalesData 的命名範圍。

SELECT *
FROM EXCEL...SalesData
GO

G. 使用用於檢索服務的 Microsoft OLE DB 提供者
此樣本建立一台連結的伺服器，並且使用 OPENQUERY 從為檢索服務啟用的連結的伺服器和檔案系統中檢索資訊。

EXEC sp_addlinkedserver FileSystem,
   'Index Server',
   'MSIDXS',
   'Web'
GO
USE pubs
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'yEmployees')
   DROP TABLE yEmployees
GO
CREATE TABLE yEmployees
 (
  id       int         NOT NULL,
  lname    varchar(30) NOT NULL,
  fname    varchar(30) NOT NULL,
  salary   money,
  hiredate datetime
 )
GO
INSERT yEmployees VALUES
 (
  10,
  'Fuller',
  'Andrew',
  $60000,
  '9/12/98'
 )
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
      WHERE TABLE_NAME = 'DistribFiles')
   DROP VIEW DistribFiles
GO
CREATE VIEW DistribFiles
 AS
 SELECT *
 FROM OPENQUERY(FileSystem,
                 'SELECT Directory,
                    FileName,
                    DocAuthor,
                    Size,
                    Create,
                    Write
                  FROM SCOPE('' "c:/My Documents" '')
                  WHERE CONTAINS(''Distributed'') > 0
                    AND FileName LIKE ''%.doc%'' ')
 WHERE DATEPART(yy, Write) = 1998
GO
SELECT *
FROM DistribFiles
GO
SELECT Directory,
  FileName,
  DocAuthor,
  hiredate
FROM DistribFiles D, yEmployees E
WHERE D.DocAuthor = E.FName + ' ' + E.LName
GO

H. 使用用於 Jet 的 Microsoft OLE DB 提供者訪問文字檔
此樣本建立一台直接存取文字檔的連結的伺服器，而沒有將這些檔案連結為 Access .mdb 檔案中的表。提供者是 Microsoft.Jet.OLEDB.4.0，提供者字串為"Text"。

資料來源是包含文字檔的目錄的完整路徑名。schema.ini 檔案（描述文字檔的結構）必須與此文字檔存在於相同的目錄中。有關建立 schema.ini 檔案的更多資訊，請參見 Jet 資料庫引擎文檔。

--Create a linked server
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'c:/data/distqry',
   NULL,
   'Text'
GO

--Set up login mappings
EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL
GO

--List the tables in the linked server
EXEC sp_tables_ex txtsrv
GO

--Query one of the tables: file1#txt
--using a 4-part name
SELECT *
FROM txtsrv...[file1#txt]

I. 使用用於 DB2 的 Microsoft OLE DB 提供者
下面的樣本建立一台名為 DB2 的連結的伺服器，該伺服器使用用於 DB2 的 Microsoft OLE DB 提供者。

EXEC sp_addlinkedserver
   @server='DB2',
   @srvproduct='Microsoft OLE DB Provider for DB2',
   @catalog='DB2',
   @provider='DB2OLEDB',
   @provstr='Initial Catalog=PUBS;Data Source=DB2;HostCCSID=1252;Network Address=XYZ;Network Port=50000;Package Collection=admin;Default Schema=admin;'

請參見

配置連結的伺服器

用 SQL Server 測試過的 OLE DB 提供者

sp_addlinkedsrvlogin

sp_addserver

sp_dropserver

sp_serveroption

sp_setnetname

系統預存程序

系統資料表