實現Sql server資料庫的遠端連線訪問

標籤：sql   資料庫   資料來源   

在同一網路環境下，或者在可以訪問的IP地址下，我們利用SQL資料庫實現遠端連線訪問可以使用如下方法：

通過調用master.sys.sp_addlinkedserver建立訪問串連

EXEC master.sys.sp_addlinkedserver @server = NULL, -- sysname    @srvproduct = N‘‘, -- nvarchar(128)    @provider = N‘‘, -- nvarchar(128)    @datasrc = N‘‘, -- nvarchar(4000)    @location = N‘‘, -- nvarchar(4000)    @provstr = N‘‘, -- nvarchar(4000)    @catalog = NULL -- sysname

[ @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 執行個體的名稱

成功建立訪問連結後就需要登陸遠程伺服器資料庫

exec master.sys.sp_addlinkedsrvlogin @rmtsrvname = NULL, -- sysname    @useself = ‘‘, -- varchar(8)    @locallogin = NULL, -- sysname    @rmtuser = NULL, -- sysname    @rmtpassword = NULL -- sysname

[ @rmtsrvname = ] ‘rmtsrvname‘
應用登入映射的連結的伺服器的名稱。 rmtsrvname 的資料類型為 sysname，無預設值。

[ @useself = ] ‘TRUE‘ | ‘FALSE‘ | ‘NULL‘
確定是否通過類比本地登入名稱或顯式提交登入名稱和密碼來串連到 rmtsrvname。 資料類型為 varchar(8)，預設值為 TRUE。

值為 TRUE 指定登入名稱使用自己的憑據串連到 rmtsrvname，而忽略 rmtuser 和 rmtpassword 參數。 FALSE 指定 rmtuser 和 rmtpassword 參數用於串連到指定 locallogin 的 rmtsrvname。 如果 rmtuser 和 rmtpassword 也設定為 NULL，則不使用登入名稱或密碼來串連連結的伺服器。

[ @locallogin = ] ‘locallogin‘
本機伺服器上的登入。 locallogin 的資料類型為 sysname，預設值為 NULL。 NULL 指定此項應用於串連到 rmtsrvname 的所有本地登入。 如果不為 NULL，則 locallogin 可以是 SQL Server 登入或 Windows 登入。 對於 Windows 登入來說，必須以直接的方式或通過已被授權訪問的 Windows 群組成員資格授予其訪問 SQL Server 的許可權。

[ @rmtuser = ] ‘rmtuser‘
當 @useself 為 FALSE 時，用於串連到 rmtsrvname 的遠程登入名稱。 當遠程伺服器是不使用 Windows 身分識別驗證的 SQL Server 執行個體時，rmtuser是一個 SQL Server 登入名。 rmtuser 的資料類型為 sysname，預設值為 NULL。

[ @rmtpassword = ] ‘rmtpassword‘
與 rmtuser 關聯的密碼。 rmtpassword 的資料類型為 sysname，預設值為 NULL。

接下來就要為遠程伺服器和連結的伺服器設定伺服器選項

    EXEC master.sys.sp_serveroption @server = NULL, -- sysname        @optname = ‘‘, -- varchar(35)        @optvalue = N‘‘ -- nvarchar(128)

[ @server = ] ‘server‘
要為其設定選項的伺服器的名稱。 server 的資料類型為 sysname，無預設值。

[ @optname = ] ‘option_name‘
為指定的伺服器設定的選項。 option_name 的資料類型為 varchar(35)，無預設值。 option_name 可以是下列任意值。

[ @optvalue =] ‘option_value‘
指定應啟用（TRUE 或 on）還是禁用（FALSE 或 off）option_name。 option_value 的資料類型為 varchar(10)，無預設值。

option_value 可以是用於 connect timeout 和 query timeout 選項的非負整數。 對於 collation name 選項，option_value 可以是定序名稱或 NULL。

通過上述步驟就可以實現遠端資料庫的訪問了

具體例子如下：

EXEC master.sys.sp_addlinkedserver@server = ‘HQDB2‘,@srvproduct = ‘‘,@provider = ‘SQLOLEDB‘,@datasrc = ‘10.82.21.69‘EXEC master.sys.sp_addlinkedsrvlogin     @rmtsrvname = ‘HQDB2‘,    @useself = N‘false‘,    @locallogin = NULL,    @rmtuser = ‘btscn‘,    @rmtpassword = ‘btscn‘ EXEC master.sys.sp_serveroption     @server = ‘HQDB2‘,     @optname = ‘RPC out‘,     @optvalue = N‘true‘EXECUTE [HQDB2].[BTSHQ9903].[dbo].[stp_Generate_RentalReport]    @GenerateDate

可以執行select * from sys.servers查詢當前的遠端存取連結是否存在

當遠端連線使用完後需要釋放

EXEC master.sys.sp_dropserver @server = NULL, -- sysname    @droplogins = ‘‘ -- char(10)

[ @server = ] ‘server‘
要刪除的伺服器。 server 的資料類型為 sysname，無預設值。 server 必須存在。

[ @droplogins = ] ‘droplogins‘ | NULL
指示如果指定了 droplogins，那麼對於 server，還必須刪除相關的遠程伺服器和連結的伺服器登入名稱。 @droplogins 的資料類型為 char(10)，預設值為 NULL。

執行個體：

EXEC master.sys.sp_dropserver     @server = ‘HQDB2‘,     @droplogins = ‘droplogins‘

有需要的童鞋可以參考一下，祝君武運昌隆。

本文出自 “To be EX” 部落格，請務必保留此出處http://gleolee.blog.51cto.com/10632675/1789039

實現Sql server資料庫的遠端連線訪問