SQL SERVER2005建Link Server

SQL Server2005中的link server類似於Oracle裡面的DB Link , 透過link server可以訪問另一個資料庫中的物件 ， 這些Link Server可以是SQL Server ， 也可以是Oracle等其他型別的資料庫， 建好Link 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，沒有預設值。

[ @srvproduct = ] 'product_name'

要新增為連結伺服器的 OLE DB 資料來源的產品名稱。product_name 的資料型別為 nvarchar(128)，預設值為 NULL。如果為 SQL Server，則不必指定 provider_name、data_source、location、provider_string 和 catalog。

[ @provider = ] 'provider_name'

與此資料來源對應的 OLE DB 訪問介面的唯一程式設計識別符號 (PROGID)。對於當前計算機中安裝的指定 OLE DB 訪問介面，provider_name 必須唯一。provider_name 的資料型別為 nvarchar(128)，預設值為 NULL；但如果忽略 provider_name，則使用 SQLNCLI。（使用 SQLNCLI 並且 SQL Server 將重定向到 SQL Server Native Client OLE DB 訪問介面的最新版本。）OLE DB 訪問介面應以指定的 PROGID 在登錄檔中註冊。

[ @datasrc = ] 'data_source'

由 OLE DB 訪問介面解釋的資料來源的名稱。data_source 的資料型別為 nvarchar(4000)。data_source 作為 DBPROP_INIT_DATASOURCE 屬性傳遞以初始化 OLE DB 訪問介面。

[ @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 或傳遞給 IDataInitialize 或設定為 DBPROP_INIT_PROVIDERSTRING 屬性以初始化 OLE DB 訪問介面。

當針對 SQL Server Native Client OLE DB 訪問介面建立連結伺服器時，可以使用 SERVER 關鍵字來指定例項，即使用 SERVER=servername\instancename 指定特定 SQL Server 例項。servername 是執行 SQL Server 的計算機的名稱，instancename 是使用者要連線的特定 SQL Server 例項的名稱。

比如我Local端有一個SQL Server  ，裡面有兩個資料庫 LocalTest和MyDataBase ， 我想在LocalTest裡面訪問MyDataBase裡面的一個表TableTest1  ， 建立過程如下 ：

1 。 開啟SQL Server2005整合開發環境 ， 連線到要建立Link Server的資料主機 ， 然後新建一個查詢 ，在查詢視窗中輸入如下的程式碼 ：
EXEC master.dbo.sp_addlinkedserver
@server = N'LINK_LOCAL',
@srvproduct=N'',
@provider = N'SQLNCLI',
@datasrc = N'(Local)'
注意 ：每個引數在賦值的時候都在前面加多一個N表示後面的值當作Unicode來處理
說明如下 ：
     @server ：link的名字，建好Link後其他資料庫就可以通過這個名字來訪問
     @srvproduct ：為空 ，引數佔位符
     @provider ：驅動程式碼
     @datasrc ：資料庫伺服器 ， 因為是本機端的 ， 所以用(Local)

第二 ：建立好這個Link之後還需要給給這個link加登陸的賬號密碼 ， 因為從外部訪問這個Link server的時候需要先登陸  ， 儲存過程如下 ：
EXEC sp_addlinkedsrvlogin 'LINK_LOCAL', 'false', null, 'account', 'password'
說明 ：第一個引數的值是上面建立的link的名稱 ， 最後兩個引數引數分別為賬號密碼  ， 執行後可以在繼承開發環境中看到新建的這個link server ,  如下圖 ：
               


第三  ： 建立好後還可以給這個LInk server加一些許可權 ：
如 ：EXEC sp_serveroption ‘REMOTE_SERVER_NAME’, ‘data access’, ‘true’


第四 ：然後我們就可以在任何需要的地方訪問這個LInk server了 ， 如 ：
select * from LINK_Local.MyDataBase.dbo.TableTest1




附 ：通過 OLE DB 訪問資料來源而建立連結伺服器的方法




連線不同資料庫例項如下 ：

A.  下例使用 SQL Server Native Client OLE DB 訪問介面在 SQL Server 例項中建立連結伺服器

EXEC sp_addlinkedserver   
   @server='S1_instance1', 
   @srvproduct='',
   @provider='SQLNCLI', 
   @datasrc='S1\instance1'

B. 使用 Microsoft OLE DB Provider for Microsoft Access

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'

C. 使用 Microsoft OLE DB Provider for Oracle

EXEC sp_addlinkedserver
   @server = 'LONDON Mktg',
   @srvproduct = 'Oracle',
   @provider = 'MSDAORA',
   @datasrc = 'MyServer'

D. 將 Microsoft OLE DB Provider for ODBC 與 data_source 引數一起使用

EXEC sp_addlinkedserver 
   @server = 'SEATTLE Payroll', 
   @srvproduct = '',
   @provider = 'MSDASQL', 
   @datasrc = 'LocalServer'

E. 將 Microsoft OLE DB Provider 用於 Excel 電子表格

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

H. 使用 Microsoft OLE DB Provider for 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 four-part name. 
SELECT * 

I. 使用 Microsoft OLE DB Provider for 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;

參考文章 ：http://msdn.microsoft.com/zh-cn/vstudio/ms190479.aspx
       Default Schema=admin;'