Sql Server 跨伺服器連線

用openrowset連線遠端SQL或插入資料

–如果只是臨時訪問,可以直接用openrowset

–查詢示例

select * from openrowset(`SQLOLEDB`, `sql伺服器名`; `使用者名稱`; `密碼`, 資料庫名.dbo.表名)

–匯入示例

select * into 表 from openrowset(`SQLOLEDB` ,`sql伺服器名`;`使用者名稱`;`密碼` ,資料庫名.dbo.表名)

–建立連結伺服器

exec sp_addlinkedserver   `srv_lnk`, “, `SQLOLEDB`,`遠端伺服器名或ip地址`

exec sp_addlinkedsrvlogin `srv_lnk`,`false`,null,`使用者名稱`,`密碼`

go

–查詢示例

select * from srv_lnk.資料庫名.dbo.表名

–匯入示例

select * into 表 from srv_lnk.資料庫名.dbo.表名

–以後不再使用時刪除連結伺服器

exec sp_dropserver `srv_lnk`,`droplogins`

go

–下面的示例訪問來自某個表的資料，該表在 SQL Server 的另一個例項中。

SELECT *FROM OPENDATASOURCE(`SQLOLEDB`,`Data Source=ServerName;User ID=MyUID;Password=MyPass`).Northwind.dbo.Categories

下面是個查詢的示例，它通過用於 Jet 的 OLE DB 提供程式查詢 Excel 電子表格。

SELECT *FROM OpenDataSource( `Microsoft.Jet.OLEDB.4.0`,`Data Source=”c:Financeaccount.xls”;User ID=Admin;Password=;Extended properties=Excel 5.0`)…xactions

由於專案需要,在開發過程遇上跨資料庫伺服器訪問資料的問題.

在網路上一搜,資料確實不少,不過解決自己的問題也花了大半天的時候,在這裡花些時間總結一下.

1.確立問題

由於需要進行跨資料庫伺服器的查詢操作,所以我個人把其定位於分散式查詢資料的問題.

2.解決方案

第一個概念.連結伺服器.

連結伺服器一般用來處理分散式查詢。當客戶端應用程式通過連結伺服器執行分散式查詢時，SQL Server 將分析該命令，並向 OLE DB 傳送行集請求。行集請求的形式可以是對提供程式執行查詢或從提供程式開啟基表。

分散式查詢可以訪問來自多種異類資料來源的資料，而這些資料可儲存在相同或不同的計算機上。Microsoft® SQL Server? 2000 通過使用 OLE DB（Microsoft 通用資料訪問應用程式介面 (API) 規範）支援分散式查詢。

連結伺服器配置允許 Microsoft® SQL Server? 對其它伺服器上的 OLE DB 資料來源執行命令。連結伺服器具有以下優點：

遠端伺服器訪問。

對整個企業內的異類資料來源執行分散式查詢、更新、命令和事務的能力。

能夠以相似的方式確定不同的資料來源。

連結伺服器元件

連結伺服器的定義指定了 OLE DB 提供程式和 OLE DB 資料來源。

OLE DB 提供程式是管理特定資料來源和與特定資料來源進行互動的動態連結庫 (DLL)。OLE DB 資料來源標識可通過 OLE DB 訪問的特定資料庫。儘管通過連結伺服器的定義所查詢的資料來源通常是資料庫，但也存在適用於多種檔案和檔案格式的 OLE DB 提供程式，包括文字檔案、電子表格資料和全文內容檢索結果。下表說明了最常用於 SQL Server 的 OLE DB 提供程式和資料來源示例。

OLE DB提供程式                                 OLE DB 資料來源

用於 SQL Server 的 Microsoft OLE DB 提供程式     SQL Server 例項

用於 Jet 的 Microsoft OLE DB 提供程式             mdb 資料庫檔案的路徑名

用於 ODBC 的 Microsoft OLE DB 提供程式           指向某個具體資料庫的 ODBC 資料來源名稱

用於 oracle 的 Microsoft OLE DB 提供程式         指向 oracle 資料庫的 SQL*Net 別名

用於索引服務的 Microsoft OLE DB 提供程式         能夠對其執行屬性搜尋或全文檢索的內容檔案

說明   SQL Server 只針對分別用於 SQL Server、Jet、Oracle、索引服務和 ODBC 的 Microsoft OLE DB 提供程式進行了測試。然而，SQL Server 分散式查詢旨在與任何實現了必需的 OLE DB 介面的 OLE DB 提供程式一起使用。

為了使資料來源能夠通過連結伺服器返回資料，那個資料來源的 OLE DB 提供程式 (DLL) 必須位於 SQL Server 所在的伺服器上。

連結伺服器一般用來處理分散式查詢。當客戶端應用程式通過連結伺服器執行分散式查詢時，SQL Server 將分析該命令，並向 OLE DB 傳送行集請求。行集請求的形式可以是對提供程式執行查詢或從提供程式開啟基表。

管理連結伺服器的定義

設定連結伺服器時，請註冊 SQL Server 的連線資訊和資料來源資訊。完成註冊後，該資料來源總可以用單個邏輯名稱(即例項名)引用。

可以使用儲存過程或 SQL Server 企業管理器來建立或刪除連結伺服器的定義。

使用儲存過程：

使用 sp_addlinkedserver 建立連結伺服器的定義。若要檢視有關給定的 SQL Server 例項中定義的連結伺服器的資訊，請使用 sp_linkedservers。有關更多資訊，請參見 sp_addlinkedserver 和 sp_linkedservers。

使用 sp_dropserver 刪除連結伺服器的定義。還可以使用此儲存過程刪除遠端伺服器。

使用 SQL Server 企業管理器：

使用 SQL Server 企業管理器控制檯樹和”連結伺服器”節點（在”安全性”資料夾內）來建立連結伺服器的定義。為連結伺服器定義名稱、提供程式屬性、伺服器選項和安全選項。有關各種為不同的 OLE DB 資料來源設定連結伺服器的方式以及要使用的引數值的更多資訊，請參見 sp_addlinkedserver。

通過右擊連結伺服器並單擊”屬性”命令，可編輯連結伺服器的定義。

通過右擊連結伺服器並單擊”刪除”命令，可刪除連結伺服器的定義。

當對連結伺服器執行分散式查詢時，請對每個要查詢的資料來源指定完全合法的、由四部分組成的表名。這個由四部分組成的名稱的格式應是：linked_server_name.catalog.schema.object_name。

   眾所周知，在大型的資料庫系統設計中，為了提升效率，不可避免的要將不用的業務放在不同的資料例項上，因此我們使用到了連結伺服器，連結伺服器為大家在不同的服務之間進行分散式資料操作提供了便利。本例子基於windows 2003作業系統，Sql server 2000(sp4)資料庫。

首先要開啟你本地伺服器和遠端伺服器的MSDTC：

開始–>Microsoft SQL Server–>服務管理器–>選擇服務中的 Distributed Transaction Coordinator（此即DTC），點選“啟動”按鈕，啟動MSDTC.

開啟以後，在本地伺服器上註冊遠端伺服器的資訊，建立linkedServer，操作程式碼如下：

exec   sp_addlinkedserver     `Far_MDB`,“,`SQLOLEDB`,`YOIIO005`,“,“,`MDB`

然後使用sp_linkedservers，檢視此伺服器是否已經成功註冊為連結伺服器，如果已經註冊成功，則使用

sp_addlinkedsrvlogin   `Far_MDB`,`false`,null,`sa`,`****`，註冊login帳號，然後你就可以執行一個Sql語句，試試看看是否可以從連結伺服器中運算元據了，

例如：

SELECT *

FROM OPENQUERY(Far_MDB, `SELECT * FROM MDB_Member`)

這就是從我的連結伺服器當中去查詢MDB_Member表的資料

備註：

1、關於連結伺服器的詳細情況請參閱聯機叢書中的sp_addlinkedserver、 sp_addlinkedsrvlogin、sp_addserver、sp_dropserver、sp_serveroption、sp_linkedservers等系統級儲存過程。

2、關於此連結伺服器的建立及其使用在不同環境下（全部內網伺服器、一內網一公網伺服器、全公網伺服器）測試，均告通過。

3、有時候在儲存過程中訪問連結伺服器的時候可能會碰到如下的提示，必須要對ANSI warning 和ANSI nulls進行設定，這時候按照如下的方法進行設定：

設定本地資料庫的屬性–>連線–>選上ANSI warning 和 ANSI nulls

如何訪問SQL Server資料庫

無論是從桌面Windows電腦上還是在Pocket PC裝置上訪問SQL Server資料庫，首先都需要建立資料庫連線。使用Visual Studio 2005開發桌面Windows應用時，開發人員可以從伺服器資源管理器中拖拉資料表到窗體上，但是.NET Compact Framework不支援資料設計器。這意味著開發人員需要程式設計實現建立資料庫連線。.NET Compact Framework的System.Data.SqlClient名稱空間同樣不支援跨伺服器的事務和連線池。設計移動應用解決方案時，應該將資料庫事務限制發生在伺服器上的單獨資料庫中。

因為.NET Compact Framework的System.Data.SqlClient名稱空間不支援資料庫連線池，所以在Pocket PC裝置上建立SQL Server資料庫連線時會產生一定的延時。在桌面Windows程式設計中，要求儘可能縮短保持與資料庫連線的時間以及減少資料庫連線次數。而在開發Pocket PC上的資料庫應用程式時，最佳策略是提前建立資料庫連線，並儘量在整個應用程式生命週期內保持資料庫連線來減少連線次數。

下面通過設計開發一個用途廣泛的Pocket PC資料庫應用程式，介紹如何訪問伺服器端資料。這個Pocket PC應用程式需要引用System.Data.Common、System.Xml和System.Data.SqlClient名稱空間。

如何用sql語句進行跨庫查詢

在機器B上的資料庫database2中建立一個臨時表#tmp，

內容就是機器A上的資料庫database1裡面的Table1。

如何用sql語句完成？（不借用dts等）

SQL code –建立連結伺服器

exec sp_addlinkedserver   `ITSV`,“,`SQLOLEDB`,`遠端伺服器名或ip地址`

exec sp_addlinkedsrvlogin `ITSV`,`false`,null,`使用者名稱`,`密碼`

–查詢示例

select * from ITSV.資料庫名.dbo.表名

–匯入示例

select * into 表 from ITSV.資料庫名.dbo.表名

–以後不再使用時刪除連結伺服器

exec sp_dropserver `ITSV`,`droplogins`

–連線遠端/區域網資料(openrowset/openquery/opendatasource)

–1、openrowset

–查詢示例

select * from openrowset(`SQLOLEDB`,`sql伺服器名`;`使用者名稱`;`密碼`,資料庫名.dbo.表名)

–生成本地表

select * into 表 from openrowset(`SQLOLEDB`,`sql伺服器名`;`使用者名稱`;`密碼`,資料庫名.dbo.表名)

–把本地表匯入遠端表

insert openrowset(`SQLOLEDB`,`sql伺服器名`;`使用者名稱`;`密碼`,資料庫名.dbo.表名)

select *from 本地表

–更新本地表

update b

set b.列A=a.列A

from openrowset(`SQLOLEDB`,`sql伺服器名`;`使用者名稱`;`密碼`,資料庫名.dbo.表名)as a inner join 本地表 b

on a.column1=b.column1

–openquery用法需要建立一個連線

–首先建立一個連線建立連結伺服器

exec sp_addlinkedserver   `ITSV`,“,`SQLOLEDB`,`遠端伺服器名或ip地址`

–查詢

select *

FROM openquery(ITSV, `SELECT *   FROM 資料庫.dbo.表名`)

–把本地表匯入遠端表

insert openquery(ITSV, `SELECT *   FROM 資料庫.dbo.表名`)

select * from 本地表

–更新本地表

update b

set b.列B=a.列B

FROM openquery(ITSV, `SELECT * FROM 資料庫.dbo.表名`) as a

inner join 本地表 b on a.列A=b.列A

–3、opendatasource/openrowset

SELECT *

FROM opendatasource(`SQLOLEDB`, `Data Source=ip/ServerName;User ID=登陸名password=密碼` ).test.dbo.roy_ta

–把本地表匯入遠端表

insert opendatasource(`SQLOLEDB`, `Data Source=ip/ServerName;User ID=登陸名password=密碼`).資料庫.dbo.表名

select * from 本地表

用 連結伺服器

OPENROWSET

OPENDATASOURCE

都可以

連結伺服器.database1.dbo.table1

連結伺服器.database1.dbo.table1

SQL code

select * into #

from openrowset(`sqloledb`,`ip`;`sa`;“,`select * from pubs.dbo.jobs`)

如果只是偶爾使用 就用opendatasource/openrowset 固定的頻繁使用建linked server