SQL Server 建立連結的伺服器

標籤：blog   http   io   ar   使用   sp   strong   on   資料   

遇到下列問題：

線上伺服器A，代理服務器B，本機伺服器C

資料在A上面，想在B上面操作類似 select * from [A].[database].table這樣的SQL，不用去連結的伺服器，直接把處理結果匯入B然後產生報表。

結果報錯如下：

訊息 7202，層級 11，狀態 2，第 1 行
在 sys.servers 中找不到伺服器 ‘A‘。請驗證指定的伺服器名稱是否正確。如果需要，請執行預存程序 sp_addlinkedserver 以將伺服器添加到 sys.servers。

解決思路：問題很明顯就是在B中沒有服務A，奇怪了B上面的本地匯入匯出就可以，為什麼這樣不可以？仔細看了才發現B上面的sqlserver連結的伺服器列表確實

不存在A。

執行下面操作在B伺服器上面建立A伺服器串連，

 

適用情境：對遠端DB進行操作。

2000與2005對比：在SQL Server 2000版本中也有連結遠程DB的SQL，但是功能比較弱，擴充性差，支援的查詢比較簡單。而SQL Server 2005版本的SSMS中已經有了 伺服器對象->連結的伺服器 的功能點，使用者首先建立一個遠程DB的連結化物件，之後就可以像本地表一樣執行表的DML了。

建立步驟：在SQL Server 2005版本開啟SSMS，伺服器對象->連結的伺服器->右擊 建立連結的伺服器，在圖2中是一種設定方式，也有其它的設定方式，比如：[圖解]sqlserver中建立連結的伺服器，圖3是安全性選項中設定遠端資料庫的帳號和密碼。

 

（圖1：建立連結） 

 

（圖2：設定連結） 

 

（圖3：設定帳號） 

 

注意事項： 在MSSQL2005中Rpc的預設設定4所示， 需要把它設定為圖5， 右鍵點擊遠程連結->屬性->伺服器選項->Rpc和Rpc Out，這兩個值需要設定為True。

 

（圖4： 預設設定） 

 

（圖5： 正確設定） 

但在MSSQL2008下不能直接修改連結的伺服器 ‘ETV2_LINK‘ 的RPC配置成TURE，可以通過語句修改如下：

 USE [master]
GO
EXEC master.dbo.sp_serveroption @server=N‘ETV2_LINK‘, @optname=N‘rpc‘, @optvalue=N‘true‘
GO
EXEC master.dbo.sp_serveroption @server=N‘ETV2_LINK‘, @optname=N‘rpc out‘, @optvalue=N‘true‘
GO 

 

產生指令碼：如果已經通過操作介面產生了 ‘ETV2_LINK‘ 的連結的伺服器，那麼我們如果需要把它移植到其它資料庫（部署、更新）的時候，就可以通過下面的方法來生產SQL指令碼，你也可以通過修改SQL指令碼來快速建立或修改連結的伺服器，比如修改@server連結的伺服器名稱，修改@datasrc遠程連結的資料庫物件。

 

（圖6： 產生SQL指令碼） 

 

SQL Server 2005產生遠程連結化物件的SQL指令碼：

 /****** 對象:  LinkedServer [ETV2_LINK]    指令碼日期: 09/08/2010 17:36:11 ******/
EXEC master.dbo.sp_addlinkedserver @server = N‘ETV2_LINK‘, @srvproduct=N‘ETV2_LINK‘, @provider=N‘SQLNCLI‘, @datasrc=N‘BWA035\BWA035_2K5‘
GO
EXEC master.dbo.sp_serveroption @server=N‘ETV2_LINK‘, @optname=N‘collation compatible‘, @optvalue=N‘false‘
GO
EXEC master.dbo.sp_serveroption @server=N‘ETV2_LINK‘, @optname=N‘data access‘, @optvalue=N‘true‘
GO
EXEC master.dbo.sp_serveroption @server=N‘ETV2_LINK‘, @optname=N‘dist‘, @optvalue=N‘false‘
GO
EXEC master.dbo.sp_serveroption @server=N‘ETV2_LINK‘, @optname=N‘pub‘, @optvalue=N‘false‘
GO
EXEC master.dbo.sp_serveroption @server=N‘ETV2_LINK‘, @optname=N‘rpc‘, @optvalue=N‘true‘
GO
EXEC master.dbo.sp_serveroption @server=N‘ETV2_LINK‘, @optname=N‘rpc out‘, @optvalue=N‘true‘
GO
EXEC master.dbo.sp_serveroption @server=N‘ETV2_LINK‘, @optname=N‘sub‘, @optvalue=N‘false‘
GO
EXEC master.dbo.sp_serveroption @server=N‘ETV2_LINK‘, @optname=N‘connect timeout‘, @optvalue=N‘0‘
GO
EXEC master.dbo.sp_serveroption @server=N‘ETV2_LINK‘, @optname=N‘collation name‘, @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N‘ETV2_LINK‘, @optname=N‘lazy schema validation‘, @optvalue=N‘false‘
GO
EXEC master.dbo.sp_serveroption @server=N‘ETV2_LINK‘, @optname=N‘query timeout‘, @optvalue=N‘0‘
GO
EXEC master.dbo.sp_serveroption @server=N‘ETV2_LINK‘, @optname=N‘use remote collation‘, @optvalue=N‘true‘ 

 

使用：假設已經建立了名為ETV2_LINK的遠程連結化物件，那麼你就可以像下面的方式來使用這個對象操作遠程DB。

使用情境1： 查詢ETV2_LINK這個遠程連結化物件的[etV2_Online]資料庫中VisiteLog_20100629表的資料。模板形如：Select * From [連結的伺服器名].[遠端資料庫名].[所有者].[表名]

--查詢遠程DB表TableName
select * from ETV2_LINK.[etV2_Online].dbo.VisiteLog_20100629

 

使用情境2： 判斷ETV2_LINK這個遠程連結化物件的[etV2_Online]資料庫中是否存在名為VisiteLog_20100629的表。

 --注意：是sys.objects不是sysobjects
--判斷遠端使用者是否存在某張表
IF EXISTS (SELECT * FROM ETV2_LINK.[etV2_Online].sys.objects WHERE name = N‘VisiteLog_20100629‘ AND type in (N‘U‘))
BEGIN
    --邏輯處理
    print ‘存在表‘
END 

 

使用情境3： 判斷遠程DB的[etV2_Online]資料庫中是否存在名為VisiteLog_20100629的表。只不過這個表名是參數化的，可以通過傳入的參數進行判斷。這裡只是簡單的設定變數的值並使用OUT來返回變數。 

 --判斷遠端使用者是否存在某張表（參數化表名），返回變數
DECLARE @IsExistTable VARCHAR(10)
DECLARE @Tablename VARCHAR(50)
DECLARE @sqlString NVARCHAR(4000)
SET @IsExistTable = ‘False‘
SET @Tablename = ‘VisiteLog_‘+convert(varchar(9),getdate()-1,112) --例如VisiteLog_20100629
SET @sqlString = 
‘IF EXISTS (SELECT * FROM ETV2_LINK.[etV2_Online].sys.objects WHERE name = N‘‘‘[email protected]+‘‘‘ AND type in (N‘‘U‘‘))
    set @IsExistTableOUT =‘‘True‘‘‘
EXEC sp_executesql @sqlString,N‘@IsExistTableOUT varchar(10) OUTPUT‘,@[email protected] OUTPUT

IF (@IsExistTable = ‘True‘)--存在
BEGIN
    --邏輯處理
    print ‘存在表‘
END 

 

補充： SQL Server 2000版本串連遠程伺服器的SQL指令碼，更多相關腳步可以參考：在T-SQL語句中訪問遠端資料庫(openrowset/opendatasource/openquery) 

--方法1：
select *  from openrowset(‘SQLOLEDB‘,‘server=192.168.0.67;uid=sa;pwd=password‘,‘SELECT * FROM BCM2.dbo.tbAppl‘)

--方法2：
select *  from openrowset(‘SQLOLEDB‘,‘192.168.0.67‘;‘sa‘;‘password‘,‘SELECT * FROM BCM2.dbo.tbAppl‘) 

SQL Server 建立連結的伺服器