曾幾何時,伙伴們為數據庫的升級傷透了腦筋.往往程序的升級趕不上數據庫的升級(版本控制的好,這也許不是什麼問題,但對於很大一部分中國公司來說這是無法避免的).而有些n久以前的數據庫要使用新程序的時候,數據庫的升級簡直就是無從下手.所以對比數據庫升級的緊要性就逐漸的凸現出來.對於表和字段的升級按道理來說是不難的,通過sysobjects與syscolumns的比較很容易的可以找到不同之處,然後增加沒有的對象即可.而對於視圖和存儲過程等非表對象的更新就有些為難了(當然視圖和存儲過程如果用手工的辦法是很簡單的).一個在於如何生成對象腳本,另一個在於如何執行.大家都知道syscomments表中藏有此類對象的腳本,人們肯定會優先考慮開采這個寶庫.當站在字符型變量最大只能存儲8000個字符時,這簡直就是一個不可逾越的障礙.人們又想從導出文本腳本然後執行這個思路著手時,又發現從字段中取出的腳本有個天然的缺陷:換行問題.(最後發現,這個問題也是可以解決的).萬般無奈下人們把渴望的目光集中到了SQLDMO上,她的身上總散發著無所不能的光芒.
當Transfer對象的美妙身材展現在人們眼前的時候,大家都對她的美麗所折服——這不正是我們所尋找的嗎?她的動人之處就在於可以把一個數據庫的對象腳本保存在內存中,然後連接到另一個數據庫上執行.太棒了!現在我們來看看她的輪廓吧：
重要屬性：
CopyAllDefaults Boolean 所有默認值
CopyAllObjects Boolean 所有對象
CopyAllRules Boolean 所有規則
CopyAllStoredProcedures Boolean 所有存儲過程
CopyAllTables Boolean 所有表
CopyAllTriggers Boolean 所有觸發器
CopyAllUserDefinedDatatypes Boolean 所有用戶自定義類型
CopyAllVIEws Boolean 所有視圖
CopyData Boolean 所有數據
DestDatabase String 目標對象數據庫
DestLogin String 目標數據庫登陸用戶名
DestPassWord String 目標數據庫登陸密碼
DestServer String 目標服務器
DestUseTrustedConnection Boolean 用戶信任連接
DropDestObjectsFirst Boolean 是否先刪除目標對象
IncludeDependencIEs Boolean 是否包含依靠對象
ScriptType Boolean 腳本類型
重要方法:
AddObject 增加對象
AddObjectByName 通過對象名稱增加對象
好了,大家應該對這個對象略有些了解了.對SQLDMO熟悉的人也許一下子就可以從中得到靈感,而初學者在這裡恐怕還是一頭霧水.不過不用著急,具體用法我們會慢慢道來:
CREATE PROCEDURE P_UDB --以源數據庫為模板升級目標數據庫
( @Source_DB sysname --原數據庫
,@Des_DB sysname --目標數據庫
,@UserName sysname --用戶名
,@psw sysname --密碼
)
AS
set nocount on
--/* 局部變量聲明
declare @ObjName sysname
,@SrvID int --服務器ＩＤ
,@DBsId int --數據庫集ID
,@transferID int --傳輸ID
,@SDBId int --源數據庫ＩＤ
,@DDBID int --目標數據庫ＩＤ
,@SVIEwListID int --源數據庫視圖列表
,@DVIEwListID int --目標數據庫視圖列表
,@str Nvarhar(4000)
,@name sysname
,@hr int --執行語句返回值
,@Error int --錯誤返回值(999:存儲過程或觸發器錯誤;9999:視圖錯誤)
--*/
--/*創建sqldmo對象 前面我們已經說過SQLDMO是個com,在SQL Server中使用OLE --自動化對象需要用到sp_OACreate等一系列的存儲過程,讀者如果有不明白的可以自--己查閱相關資料
exec @hr=sp_oacreate 'SQLDMO.sqlserver',@SrvID output
if @hr<>0
begin
set @Error=1
goto PEnd
end
--*/
--/*連接服務器
exec @hr=sp_oamethod @SrvID,'connect',null,@@ServerName,@UserName,@psw
if @hr<>0
begin
set @Error=2
goto PEnd
end
--*/
--/*取數據庫集
exec @hr=sp_oagetproperty @SrvID,'databases',@DBsId output
--*/
--/*選擇源數據庫
exec @hr=sp_oamethod @DBsId,'item',@SDBId output,@Source_DB
if @hr<>0
begin
set @Error=3
goto PEnd
end
--*/
--/*選擇目標數據庫
exec @hr=sp_oamethod @DBsId,'item',@DDBId output,@Des_DB
if @hr<>0
begin
set @Error=4
goto PEnd
end
--*/
/*Tansfer屬性設置(生成三大對象)
exec @hr=sp_oacreate 'SQLDMO.Transfer',@transferID output
exec @hr=sp_oasetproperty @transferID,'DestServer',@@ServerName
exec @hr=sp_oasetproperty @transferID,'DestLogin',@UserName
exec @hr=sp_oasetproperty @transferID,'DestPassWord ',@psw
exec @hr=sp_oasetproperty @transferID,'DestDatabase',@des_DB
exec @hr=sp_oasetproperty @transferID,'DropDestObjectsFirst ',1
exec @hr=sp_oasetproperty @transferID,'CopyAllStoredProcedures ',1
exec @hr=sp_oasetproperty @transferID,'CopyAllTriggers',1
exec @hr=sp_oasetproperty @transferID,'CopyAllVIEws',1
--exec @hr=sp_oasetproperty @transferID,'ScriptType ',1 這裡大家可以試試這個屬性
exec @hr=sp_oamethod @DDBId,'Transfer ',null,@transferID
exec sp_OADestroy @TransferID
if @hr<>0
begin
set @Error=10
goto PEnd
end
*/
PEnd:
exec @hr = sp_OAMethod @SrvID, 'DisConnect'
exec sp_OADestroy @SrvIDprint (@Error)
Return (@Error)
GO
上邊的存儲過程只要是介紹方法的實現,而具體的功能比如表及字段的比較生成這裡就省略了.
其實有一個大家最後也沒能解決好的問題就是對象依賴的問題.許多對象比如視圖裡嵌視圖,這時生成與執行需要有順序的.雖然有算法但有缺陷.希望讀者能可以提供好的算法.
