在SQL SERVER 中使用SQLDMO
类别: 数据库教程 
曾几何时,伙伴们为数据库的升级伤透了脑筋.往往程序的升级赶不上数据库的升级(版本控制的好,这也许不是什么问题,但对于很大一部分中国公司来说这是无法避免的).而有些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 @SrvID
print (@Error)
Return (@Error)
GO
上边的存储过程只要是介绍方法的实现,而具体的功能比如表及字段的比较生成这里就省略了.
其实有一个大家最后也没能解决好的问题就是对象依赖的问题.许多对象比如视图里嵌视图,这时生成与执行需要有顺序的.虽然有算法但有缺陷.希望读者能可以提供好的算法.
当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 @SrvID
print (@Error)
Return (@Error)
GO
上边的存储过程只要是介绍方法的实现,而具体的功能比如表及字段的比较生成这里就省略了.
其实有一个大家最后也没能解决好的问题就是对象依赖的问题.许多对象比如视图里嵌视图,这时生成与执行需要有顺序的.虽然有算法但有缺陷.希望读者能可以提供好的算法.
