一.摘要

本文通過(guò)實(shí)際業(yè)務(wù)場(chǎng)景講解如何實(shí)現(xiàn)跨數(shù)據(jù)庫(kù)服務(wù)器的數(shù)據(jù)查詢, 以及跨表更新的SQL語(yǔ)法.

二.業(yè)務(wù)場(chǎng)景

想實(shí)現(xiàn)的功能很簡(jiǎn)單, 在我的本地一個(gè)表用來(lái)保存省的信息: T_Province

在另外一臺(tái)服務(wù)器上也有一個(gè)保存省的表province,其中有我本地沒(méi)有的provience_name_en和provience_id信息.我希望將它們保存到我的表中.

雖然用程序很容易實(shí)現(xiàn),但是我覺(jué)得如果能直接操作數(shù)據(jù)庫(kù)的話將更簡(jiǎn)單,而且還能學(xué)習(xí)到跨服務(wù)器查詢的知識(shí).

三.準(zhǔn)備工作

首先我在本地 T_Province 表中添加了 ProvinceNameEn 和 ProvinceId 兩個(gè)字段.接下來(lái)就要想辦法為這兩個(gè)字段填充數(shù)據(jù).

四.跨服務(wù)器查詢

首先需要解決跨服務(wù)器查詢的問(wèn)題. 先來(lái)看我的最終實(shí)現(xiàn):

--創(chuàng)建鏈接服務(wù)器exec sp_addlinkedserver@server= 'SQL2',@srvproduct= '',@provider='SQLNCLI', @datasrc = '192.168.9.123'--登錄鏈接服務(wù)器exec sp_addlinkedsrvlogin @rmtsrvname = 'SQL2',@useself = 'false ',@locallogin = null, @rmtuser ='sa', @rmtpassword = '123456' 

 
上面使用sp_addlinkedserver和sp_addlinkedsrvlogin 與服務(wù)器建立了鏈接, 接下來(lái)就可以直接查詢遠(yuǎn)程服務(wù)器上的數(shù)據(jù)了:

--創(chuàng)建臨時(shí)表create table  #t  (ProvinceName nvarchar(50), ProvinceNameEn nvarchar(50), ProvinceID nvarchar(50))INSERT INTO #t(ProvinceName, ProvinceNameEn, ProvinceID)(    SELECT localDB.ProvinceName,  serverDB.province_name_en,  serverDB.province_ID      FROM T_Province as localDB,  SQL2.bdg_web_retail.dbo.province as serverDB    WHERE localDB.ProvinceName =  serverDB.Province_Name)--跨服務(wù)器查詢生成的臨時(shí)表結(jié)果SELECT * FROM #t 

通過(guò)上面的SQL語(yǔ)句,我將兩個(gè)服務(wù)器,兩個(gè)數(shù)據(jù)庫(kù)的兩個(gè)表做了內(nèi)聯(lián)查詢,并且將結(jié)果保存到了本地的臨時(shí)表#t中.

五.跨表更新

接下來(lái)希望將#t 中的數(shù)據(jù)更新到T_Province表中.其實(shí)跨表更新很簡(jiǎn)單, 但是一開(kāi)始頭腦中這個(gè)概念, 不知道set子句如何寫(xiě).下面是最后的成果:

--更新本地的 T_Province表數(shù)據(jù)UPDATE T_ProvinceSET T_Province.ProvinceNameEn =  ( SELECT #t.ProvinceNameEn),    T_Province.ProvinceID = (SELECT #t.ProvinceID)FROM T_Province, #tWHERE T_Province.ProvinceName = #t.ProvinceName

需要注意的是我最開(kāi)始使用了Declare建立表變量的形式創(chuàng)建了@t,但是執(zhí)行update操作時(shí)提示"必須聲明標(biāo)量變量@t", 換成了臨時(shí)表#t就沒(méi)有問(wèn)題.

六.跨服務(wù)器查詢相關(guān)知識(shí)

下面對(duì)跨服務(wù)器查詢用到的知識(shí)進(jìn)行講解.

創(chuàng)建鏈接服務(wù)器 sp_addlinkedserver

創(chuàng)建鏈接服務(wù)器。鏈接服務(wù)器讓用戶可以對(duì) OLE DB 數(shù)據(jù)源進(jìn)行分布式異類(lèi)查詢。在使用 sp_addlinkedserver 創(chuàng)建鏈接服務(wù)器后，可對(duì)該服務(wù)器運(yùn)行分布式查詢。如果鏈接服務(wù)器定義為 SQL Server 實(shí)例，則可執(zhí)行遠(yuǎn)程存儲(chǔ)過(guò)程。

語(yǔ)法

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

參數(shù)

[ @server = ] 'server'

要?jiǎng)?chuàng)建的鏈接服務(wù)器的名稱(chēng)。server 的數(shù)據(jù)類(lèi)型為 sysname，沒(méi)有默認(rèn)值。

[ @srvproduct = ] 'product_name'

要添加為鏈接服務(wù)器的 OLE DB 數(shù)據(jù)源的產(chǎn)品名稱(chēng)。product_name 的數(shù)據(jù)類(lèi)型為 nvarchar(128)，默認(rèn)值為 NULL。如果為 SQL Server，則不必指定 provider_name、data_source、location、provider_string 和 catalog。

[ @provider = ] 'provider_name'

與此數(shù)據(jù)源對(duì)應(yīng)的 OLE DB 訪問(wèn)接口的唯一編程標(biāo)識(shí)符 (PROGID)。對(duì)于當(dāng)前計(jì)算機(jī)中安裝的指定 OLE DB 訪問(wèn)接口，provider_name 必須唯一。provider_name 的數(shù)據(jù)類(lèi)型為 nvarchar(128)，默認(rèn)值為 NULL；但如果忽略 provider_name，則使用 SQLNCLI。SQLNCLI 是 SQL 本機(jī) OLE DB 訪問(wèn)接口。OLE DB 訪問(wèn)接口應(yīng)以指定的 PROGID 在注冊(cè)表中注冊(cè)。

[ @datasrc = ] 'data_source'

由 OLE DB 訪問(wèn)接口解釋的數(shù)據(jù)源的名稱(chēng)。data_source 的數(shù)據(jù)類(lèi)型為 nvarchar(4000)。data_source 作為 DBPROP_INIT_DATASOURCE 屬性傳遞以初始化 OLE DB 訪問(wèn)接口。

[ @location = ] 'location'

由 OLE DB 訪問(wèn)接口解釋的數(shù)據(jù)庫(kù)的位置。location 的數(shù)據(jù)類(lèi)型為 nvarchar(4000)，默認(rèn)值為 NULL。location 作為 DBPROP_INIT_LOCATION 屬性傳遞以初始化 OLE DB 訪問(wèn)接口。

[ @provstr = ] 'provider_string'

OLE DB 訪問(wèn)接口特定的連接字符串，它可標(biāo)識(shí)唯一的數(shù)據(jù)源。provider_string 的數(shù)據(jù)類(lèi)型為 nvarchar(4000)，默認(rèn)值為 NULL。provstr 或傳遞給 IDataInitialize 或設(shè)置為 DBPROP_INIT_PROVIDERSTRING 屬性以初始化 OLE DB 訪問(wèn)接口。

在針對(duì) SQL 本機(jī)客戶端 OLE DB 訪問(wèn)接口創(chuàng)建鏈接服務(wù)器后，可將 SERVER 關(guān)鍵字用作 SERVER=servername\instancename 來(lái)指定實(shí)例，以指定特定的 SQL Server 實(shí)例。servername 是運(yùn)行 SQL Server 的計(jì)算機(jī)名稱(chēng)，instancename 是用戶將連接到的特定 SQL Server 實(shí)例的名稱(chēng)。

[ @catalog = ] 'catalog'

與 OLE DB 訪問(wèn)接口建立連接時(shí)所使用的目錄。catalog 的數(shù)據(jù)類(lèi)型為 sysname，默認(rèn)值為 NULL。catalog 作為 DBPROP_INIT_CATALOG 屬性傳遞以初始化 OLE DB 訪問(wèn)接口。在針對(duì) SQL Server 實(shí)例定義鏈接服務(wù)器時(shí)，目錄指向鏈接服務(wù)器映射到的默認(rèn)數(shù)據(jù)庫(kù)。

登錄鏈接服務(wù)器 sp_addlinkedsrvlogin

語(yǔ)法

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'      [ , [ @useself = ] 'useself' ]      [ , [ @locallogin = ] 'locallogin' ]      [ , [ @rmtuser = ] 'rmtuser' ]      [ , [ @rmtpassword = ] 'rmtpassword' ] 

參數(shù)

[ @rmtsrvname = ] 'rmtsrvname'

應(yīng)用登錄映射的鏈接服務(wù)器的名稱(chēng)。rmtsrvname 的數(shù)據(jù)類(lèi)型為 sysname，沒(méi)有默認(rèn)值。

[ @useself = ] 'useself'

確定用于連接遠(yuǎn)程服務(wù)器的登錄名。useself 的數(shù)據(jù)類(lèi)型為 varchar(8)，默認(rèn)值為 TRUE。

值為 true 時(shí)指定登錄使用自己的憑據(jù)連接 rmtsrvname，忽略 rmtuser 和 rmtpassword 參數(shù)。false 指定使用 rmtuser 和 rmtpassword 參數(shù)連接指定 locallogin 的 rmtsrvname。如果 rmtuser 和 rmtpassword 也設(shè)置為 NULL，則不使用登錄名或密碼來(lái)連接鏈接服務(wù)器。

[ @locallogin = ] 'locallogin'

本地服務(wù)器上的登錄。locallogin 的數(shù)據(jù)類(lèi)型為 sysname，默認(rèn)值為 NULL。NULL 指定此項(xiàng)應(yīng)用于連接到 rmtsrvname 的所有本地登錄。如果不為 NULL，則 locallogin 可以是 SQL Server 登錄或 Windows 登錄。對(duì)于 Windows 登錄來(lái)說(shuō)，必須以直接的方式或通過(guò)已被授權(quán)訪問(wèn)的 Windows 組成員身份授予其訪問(wèn) SQL Server 的權(quán)限。

[ @rmtuser = ] 'rmtuser'

當(dāng) useself 為 false 時(shí)，表示用于連接 rmtsrvname 的用戶名。rmtuser 的數(shù)據(jù)類(lèi)型為 sysname，默認(rèn)值為 NULL。

[ @rmtpassword = ] 'rmtpassword'

與 rmtuser 關(guān)聯(lián)的密碼。rmtpassword 的數(shù)據(jù)類(lèi)型為 sysname，默認(rèn)值為 NULL。

使用鏈接服務(wù)器

服務(wù)器名.數(shù)據(jù)庫(kù)名.dbo.表名

刪除鏈接服務(wù)器 sp_dropserver

語(yǔ)法

sp_dropserver [ @server = ] 'server'      [ , [ @droplogins = ] { 'droplogins' | NULL} ]

參數(shù)

[ @server = ] 'server'

要?jiǎng)h除的服務(wù)器。server 的數(shù)據(jù)類(lèi)型為 sysname，無(wú)默認(rèn)值。server 必須存在。

[ @droplogins = ] 'droplogins' | NULL

指示如果指定了 droplogins，那么對(duì)于 server，還必須刪除相關(guān)的遠(yuǎn)程服務(wù)器和鏈接服務(wù)器登錄名。@droplogins 的數(shù)據(jù)類(lèi)型為 char(10)，默認(rèn)值為 NULL。