適用場景:對遠程的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'''+@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')
作者:聽風吹雨