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腳本:
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 ''' + @Tablename + ''' AND type in (N '' U '' ))
set @IsExistTableOUT = '' True '''
EXEC sp_executesql @sqlString ,N ' @IsExistTableOUT varchar(10) OUTPUT ' , @IsExistTableOUT = @IsExistTable 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 ' )