在SQL Server 中使用SQLDMO

曾几何时,伙伴们为数据库的升级伤透了脑筋.往往程序的升级赶不上数据库的升级(版本控制的好,这也许不是什么问题,但对于很大一部分中国公司来说这是无法避免的).而有些n久以前的数据库要使用新程序的时候,数据库的升级简直就是无从下手.所以对比数据库升级的紧要性就逐渐的凸现出来.对于表和字段的升级按道理来说是不难的,通过sysobjects与syscolumns的比较很容易的可以找到不同之处,然后增加没有的对象即可.而对于视图和存储过程等非表对象的更新就有些为难了(当然视图和存储过程如果用手工的办法是很简单的).一个在于如何生成对象脚本,另一个在于如何执行.大家都知道syscomments表中藏有此类对象的脚本,人们肯定会优先考虑开采这个宝库.当站在字符型变量最大只能存储8000个字符时,这简直就是一个不可逾越的障碍.人们又想从导出文本脚本然后执行这个思路着手时,又发现从字段中取出的脚本有个天然的缺陷:换行问题.(最后发现,这个问题也是可以解决的).万般无奈下人们把渴望的目光集中到了SQLDMO上,她的身上总散发着无所不能的光芒.

当Transfer对象的美妙身材展现在人们眼前的时候,大家都对她的美丽所折服——这不正是我们所寻找的吗?她的动人之处就在于可以把一个数据库的对象脚本保存在内存中,然后连接到另一个数据库上执行.太棒了!现在我们来看看她的轮廓吧：

重要属性：

CopyAllDefaults Boolean;;所有默认值

CopyAllObjects; Boolean;;所有对象

CopyAllRules;;Boolean;;所有规则

CopyAllStoredProceduresBoolean;所有存储过程

CopyAllTables;Boolean;;所有表

CopyAllTriggers Boolean;;所有触发器

CopyAllUserDefinedDatatypes;;;Boolean;;;;所有用户自定义类型

CopyAllViews;;Boolean;;所有视图

CopyDataBoolean;;所有数据

DestDatabase;String;;;;目标对象数据库

DestLogin; String;;;;目标数据库登陆用户名

DestPassword;;String;;;;目标数据库登陆密码

DestServer String;;;;目标服务器

DestUseTrustedConnection; Boolean; 用户信任连接

DropDestObjectsFirst; Boolean;;是否先删除目标对象

IncludeDependenciesBoolean;;是否包含依靠对象

ScriptType; Boolean;;脚本类型

重要方法:

AddObject增加对象

AddObjectByName;通过对象名称增加对象

好了,大家应该对这个对象略有些了解了.对SQLDMO熟悉的人也许一下子就可以从中得到灵感,而初学者在这里恐怕还是一头雾水.不过不用着急,具体用法我们会慢慢道来:

CREATE PROCEDURE; P_UDB --以源数据库为模板升级目标数据库

(; @Source_DB; sysname;;--原数据库

,@Des_DB;;sysname;;--目标数据库

,@UserNamesysname;;--用户名

,@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;--执行语句返回值

,@Errorint;;;;--错误返回值(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 @SrvID;

print (@Error)

Return (@Error)

GO

上边的存储过程只要是介绍方法的实现,而具体的功能比如表及字段的比较生成这里就省略了.

其实有一个大家最后也没能解决好的问题就是对象依赖的问题.许多对象比如视图里嵌视图,这时生成与执行需要有顺序的.虽然有算法但有缺陷.希望读者能可以提供好的算法.