快捷導(dǎo)航

簡(jiǎn)單實(shí)用SQL腳本Part SQLServer 2005 鏈接服務(wù)器

更新時(shí)間：2010年09月12日 22:12:28 作者：

SQL Server 2005版本的SSMS中已經(jīng)有了服務(wù)器對(duì)象->鏈接服務(wù)器的功能點(diǎn)，用戶(hù)首先創(chuàng)建一個(gè)遠(yuǎn)程DB的鏈接對(duì)象，之后就可以像本地表一樣執(zhí)行表的DML了。

適用場(chǎng)景：對(duì)遠(yuǎn)程的DB進(jìn)行操作。

2000與2005對(duì)比：在SQL Server 2000版本中也有鏈接遠(yuǎn)程DB的SQL，但是功能比較弱，擴(kuò)展性差，支持的查詢(xún)比較簡(jiǎn)單。而SQL Server 2005版本的SSMS中已經(jīng)有了 服務(wù)器對(duì)象->鏈接服務(wù)器 的功能點(diǎn)，用戶(hù)首先創(chuàng)建一個(gè)遠(yuǎn)程DB的鏈接對(duì)象，之后就可以像本地表一樣執(zhí)行表的DML了。

創(chuàng)建步驟：在SQL Server 2005版本打開(kāi)SSMS，服務(wù)器對(duì)象->鏈接服務(wù)器->右擊新建鏈接服務(wù)器，在圖2中是一種設(shè)置方式，也有其它的設(shè)置方式，比如：[圖解]sqlserver中創(chuàng)建鏈接服務(wù)器，圖3是安全性選項(xiàng)中設(shè)置遠(yuǎn)程數(shù)據(jù)庫(kù)的賬號(hào)和密碼。

（圖1：新建鏈接）

（圖2：設(shè)置鏈接）

（圖3：設(shè)置帳號(hào)）

注意事項(xiàng)： 在MSSQL2005中Rpc的默認(rèn)設(shè)置如圖4所示，需要把它設(shè)置為圖5，右鍵點(diǎn)擊遠(yuǎn)程鏈接->屬性->服務(wù)器選項(xiàng)->Rpc和Rpc Out，這兩個(gè)值需要設(shè)置為True。

（圖4：默認(rèn)設(shè)置）

（圖5：正確設(shè)置）

但在MSSQL2008下不能直接修改鏈接服務(wù)器 'ETV2_LINK' 的RPC配置成TURE，可以通過(guò)語(yǔ)句修改如下：

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

生成腳本：如果已經(jīng)通過(guò)操作界面生成了 'ETV2_LINK' 的鏈接服務(wù)器，那么我們?nèi)绻枰阉浦驳狡渌鼣?shù)據(jù)庫(kù)（部署、更新）的時(shí)候，就可以通過(guò)下面的方法來(lái)生產(chǎn)SQL腳本，你也可以通過(guò)修改SQL腳本來(lái)快速新建或修改鏈接服務(wù)器，比如修改@server鏈接服務(wù)器名稱(chēng)，修改@datasrc遠(yuǎn)程鏈接的數(shù)據(jù)庫(kù)對(duì)象。

（圖6：生成SQL腳本）

SQL Server 2005生成遠(yuǎn)程鏈接對(duì)象的SQL腳本：

/****** 對(duì)象: 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'

使用：假設(shè)已經(jīng)創(chuàng)建了名為ETV2_LINK的遠(yuǎn)程鏈接對(duì)象，那么你就可以像下面的方式來(lái)使用這個(gè)對(duì)象操作遠(yuǎn)程DB。

使用場(chǎng)景1： 查詢(xún)ETV2_LINK這個(gè)遠(yuǎn)程鏈接對(duì)象的[etV2_Online]數(shù)據(jù)庫(kù)中VisiteLog_20100629表的數(shù)據(jù)。模板形如：Select * From [鏈接服務(wù)器名].[遠(yuǎn)程數(shù)據(jù)庫(kù)名].[所有者].[表名]

--查詢(xún)遠(yuǎn)程DB表TableName
select * from ETV2_LINK.[etV2_Online].dbo.VisiteLog_20100629

使用場(chǎng)景2： 判斷ETV2_LINK這個(gè)遠(yuǎn)程鏈接對(duì)象的[etV2_Online]數(shù)據(jù)庫(kù)中是否存在名為VisiteLog_20100629的表。

--注意：是sys.objects不是sysobjects
--判斷遠(yuǎn)程用戶(hù)是否存在某張表
IF EXISTS (SELECT * FROM ETV2_LINK.[etV2_Online].sys.objects WHERE name = N'VisiteLog_20100629' AND type in (N'U'))
BEGIN
--邏輯處理
print '存在表'
END

使用場(chǎng)景3： 判斷遠(yuǎn)程DB的[etV2_Online]數(shù)據(jù)庫(kù)中是否存在名為VisiteLog_20100629的表。只不過(guò)這個(gè)表名是參數(shù)化的，可以通過(guò)傳入的參數(shù)進(jìn)行判斷。這里只是簡(jiǎn)單的設(shè)置變量的值并使用OUT來(lái)返回變量。

--判斷遠(yuǎn)程用戶(hù)是否存在某張表（參數(shù)化表名），返回變量
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

補(bǔ)充： SQL Server 2000版本連接遠(yuǎn)程服務(wù)器的SQL腳本，更多相關(guān)腳步可以參考：在T-SQL語(yǔ)句中訪(fǎng)問(wèn)遠(yuǎn)程數(shù)據(jù)庫(kù)(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')
作者：聽(tīng)風(fēng)吹雨

您可能感興趣的文章: