Miscrosoft SQL Server建立連結伺服器

連接到另外一台電腦中的SQL Server，首先建立

1. 開啟防火牆中通訊埠

防火牆系統有助於預防未經授權存取電腦資源。 若要在防火牆開啟時

如果在從防火牆開啟時，要連接到另一部電腦的 [SQL Server] ，您必須開啟防火牆中的通訊埠。

在設定 Database Engine 使用固定通訊埠之後，請遵循下列指示，在「Windows 防火牆」中開啟該通訊埠 (您不需要為預設執行個體設定固定通訊埠，因為它已經固定在 TCP 通訊埠 1433)。
若要在 Windows 防火牆中開啟通訊埠供 TCP 存取 (Windows 7)

1. 在 [開始] 功能表上、按一下 [執行]，輸入 WF.msc，然後按一下 [確定]。

2. 在 [具有進階安全性的 Windows 防火牆] 的左窗格中，以滑鼠右鍵按一下 [輸入規則]，再從動作窗格按一下 [新增規則]。

3. 在 [規則類型] 對話方塊中，選取 [通訊埠]，然後按 [下一步]。

4. 在 [通訊協定及連接埠] 對話方塊中，選取 [TCP]。 選取 [特定本機連接埠]，然後輸入 Database Engine 執行個體的通訊埠編號。 輸入 1433 表示預設執行個體。 如果您要設定具名執行個體，而且在上一項工作中已設定固定通訊埠，請輸入 49172 。 按 [下一步] 。

5. 在 [執行動作] 對話方塊中，選取 [允許連線]，然後按 [下一步]。

6. 在 [設定檔] 對話方塊中，選取您想要連線至 Database Engine時，描述電腦連線環境的設定檔，然後按 [下一步]。

7. 在 [名稱] 對話方塊中，輸入此規則的名稱和描述，然後按一下 [完成]。

如需防火牆設定 (包括 Windows Vista的指示) 的詳細資訊，請參閱 設定用於 Database Engine 存取的 Windows 防火牆。 如需預設 Windows 防火牆設定的詳細資訊以及影響 Database Engine、Analysis Services、Reporting Services 和 Integration Services 之 TCP 通訊埠的描述，請參閱 設定 Windows 防火牆以允許 SQL Server 存取。

 

打開 Microsoft SQL Server Management Studio 介面，輸入帳號、密碼應該就可以連結 Database Engine 了。

 

2. 建立連結伺服器

1. 開啟[SQL SERVER Management Studio]介面

2. 點選[伺服器物件]後，在[連結的伺服器]上[右鍵]，點選[新增連結的伺服器]

3. 在[新增連結的伺服器]視窗中的[一般]頁籤依序輸入如下欄位

  • 連結的伺服器：輸入[AR]
  • 伺服器類型：點選[其他資料來源]
  • 提供者：下拉選單選[Microsoft OLE DB Provider for SQL Server]
  • 產品名稱：輸入[SQLNCLI]
  • 資料來源：輸入B主機IP[10.48.16.10]

4. 在[新增連結的伺服器]視窗中的[安全性]頁籤依序輸入如下欄位，輸入完後按下[確定

點選[使用此安全性內容建立]
遠端登入：[User Name]->此User在B資料庫主機上需要有權限
指定密碼：[Password]->User密碼

以上程序完成後便可使用此DB Link，例如：SELECT * from [AR]..[B資料庫名稱].[dbo].[TABLE NAME]

使用T-SQL
             USE [master]
             GO
             EXEC master.dbo.sp_addlinkedserver
                @server = N'AR',
                @srvproduct=N'SQLNCLI',
                @provider=N'SQLOLEDB',
                @datasrc=N'10.48.16.10'
             GO
             USE [master]
             GO
             EXEC master.dbo.sp_addlinkedsrvlogin
                @rmtsrvname = N'AR',
                @locallogin = NULL ,
                @useself = N'False',
                @rmtuser = N'User Name',
                @rmtpassword = N'Password'
            GO

            P.S. sp_addlinkedserver 參數說明
                    @server = N'AR',  連結的伺服器
                    @srvproduct=N'a',  產品名稱(SQLNCLI)
                    @provider=N'SQLOLEDB', 提供者
                    @datasrc=N'b',   資料來源(預設執行個體)
                    @provstr=N'c',   提供者字串(可不填)
                    @catalog=N'd'   目錄(資料庫名稱,可不填)

 

3. sp_addlinkedserver 、 sp_dropserver 使用方法

1. sp_addlinkedserver

建立連結伺服器。連結伺服器可讓您對 OLE DB 資料來源存取分散式異質性查詢。

sp_addlinkedserver
    @server = 'server' ,
        -- 要建立的連結伺服器名稱。
    @srvproduct = 'product_name' ,
        -- 加入的 OLE DB 資料來源產品名稱。
        -- nvarchar(128)，預設值是 NULL。
    @provider = 'provider_name' ,
        -- 唯一程式化識別碼 (PROGID)。
        -- nvarchar(128)，預設值是 NULL。
    @datasrc = 'data_source' ,
        -- 資料來源名稱。
        -- nvarchar(4000)，DBPROP_INIT_DATASOURCE 屬性。
    @location = 'location' ,
        -- 資料庫位置。
        -- nvarchar(4000)，預設值是 NULL，DBPROP_INIT_LOCATION 屬性。
    @provstr = 'provider_string' ,
        -- 連接字串，用來識別唯一資料來源。
        -- nvarchar(4000)，預設值是 NULL，DBPROP_INIT_PROVIDERSTRING 屬性。
    @catalog = 'catalog'
        -- 所用的目錄。
        -- 是 sysname，預設值是 NULL，DBPROP_INIT_CATALOG 屬性。

傳回碼值：0 (成功) 或 1 (失敗)

權限：需要伺服器的 ALTER ANY LINKED SERVER 權限。

* 資料來源產品名稱如果是 SQL Server，則不必指定 provider_name、data_source、location、provider_string 和 catalog。

* 連結伺服器資料紀錄於 master.dbo.sysservers 中。

2. sp_dropserver

從 SQL Server 本機執行個體上的已知遠端和連結伺服器清單中移除伺服器。

sp_dropserver
    @server = 'server' ,
        -- 要移除的伺服器
    @droplogins = {'droplogins' | NULL}
        -- 如果指定了 droplogins，也必須移除 server 的相關遠端和連結伺服器登入
        -- char(10)，預設值是 NULL。
傳回碼值：0 (成功) 或 1 (失敗)

權限：需要伺服器的 ALTER ANY LINKED SERVER 權限。

* 若要在移除伺服器時移除伺服器的所有遠端和連結伺服器登入，請使用 droplogins 引數。

[範例] 連接到 SQL Server，建立一個名叫 SEATTLESales 的連結伺服器。

 EXEC sp_addlinkedserver
     'SEATTLESales',
     N'SQL Server'
[範例] 連接到 Microsoft Access 2002-2003

 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'
[範例] 連接到 Microsoft Access 2007

 EXEC sp_addlinkedserver
    @server = 'SEATTLE Mktg',
    @provider = Microsoft.ACE.OLEDB.12.0',
    @srvproduct = 'OLE DB Provider for ACE',
    @datasrc = 'C:\MSOffice\Access\Samples\Northwind.accdb'
[範例] 連接到 Microsoft Excel 1997 - 2003

 EXEC sp_addlinkedserver
    'ExcelShare',
    'Jet 4.0',
    'Microsoft.Jet.OLEDB.4.0',
    '\\MyServer\MyShare\Spreadsheets\DistExcl.xls',
    NULL,
    'Excel 5.0'

[範例] 連接到 Microsoft Excel 2007

EXEC sp_addlinkedserver
    @server = N'ExcelDataSource',
    @srvproduct=N'ExcelData',
    @provider=N'Microsoft.ACE.OLEDB.12.0',
    @datasrc=N'C:\DataFolder\People.xlsx',
    @provstr='EXCEL 12.0' ;
[範例] 連接到 Oracle，建立一個名為 LONDON Mktg 的連結伺服器

 EXEC sp_addlinkedserver
    @server = 'LONDON Mktg',
    @srvproduct = 'Oracle',
    @provider = 'MSDAORA',
    @datasrc = 'MyServer'
[範例] 連接到 ODBC，建立一個名為 SEATTLE Payroll 的連結伺服器

 EXEC sp_addlinkedserver
    @server = 'SEATTLE Payroll',
    @srvproduct = '',
    @provider = 'MSDASQL',
    @datasrc = 'LocalServer'
[範例] 連接到 DB2

 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;'
[範例] 連接到 文字檔

 --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 four-part name.
SELECT *
FROM txtsrv...[file1#txt]
[範例] 移除遠端伺服器 ACCOUNTS 和所有相關聯的遠端登入。

 EXEC sp_dropserver 'ACCOUNTS', 'droplogins';
 

 

 

  • Top