|
楼主 |
发表于 2011-11-24 00:57:02
|
显示全部楼层
860[Main]升级
关闭了系统数据库连接
重新打开了系统数据库连接
开始读取升级列表
结束读取升级列表
开始事务
数据库名: UFDATA_001_2006
D:\U8SOFT\Admin\SQLFILE8600\Main\Ufdata\DSTR860_Public.sql
错误信息:
-2147217900
对象 'PersonMSG_PK' 依赖于 列 'cPersonCode'。
执行如下语句时出错:
--===========郑芸部分(End)======================
--===========侯玉保部分(Begin)======================
--业务员(Person)
if ((select length from syscolumns where id=OBJECT_ID('Person') and name='cPersonCode')<20)
begin
declare @fConst varchar (100)
declare @fTbl varchar (100),@fCol varchar (100)
declare @rTbl varchar (100),@rCol varchar (100)
select object_name(constid) as col1,object_name(fkeyid) as col2,object_name(rkeyid) as col3,
col_name(fkeyid,fkey) as col4,col_name(rkeyid,rkey) as col5
into tempdb..tblfkey
from sysforeignkeys
where object_name(rkeyid)='Person' and col_name(rkeyid,rkey)='cPersonCode'
declare Rst cursor for select * from tempdb..tblfkey
--删除关系并改变长度
open Rst
fetch next from Rst into @fConst, @fTbl, @rTbl, @fCol,@rCol
while @@fetch_status=0
begin
exec ('ALTER TABLE ' + @fTbl + ' Drop Constraint ' + @fConst)
exec ('ALTER TABLE ' + @fTbl + ' alter column ' + @fCol + ' varchar(20) NULL')
fetch next from Rst into @fConst, @fTbl, @rTbl, @fCol,@rCol
end
ALTER TABLE Person alter column cPersonCode varchar(20) NOT NULL
close Rst
--恢复原来的关系
open Rst
fetch next from Rst into @fConst, @fTbl, @rTbl, @fCol,@rCol
while @@fetch_status=0
begin
print @fConst
exec ('ALTER TABLE ' + @fTbl + ' ADD
CONSTRAINT ' + @fConst + ' FOREIGN KEY
( ' +
@fCol
+ ') REFERENCES ' + @rTbl + ' ( ' +
@rCol
+ ')')
fetch next from Rst into @fConst, @fTbl, @rTbl, @fCol,@rCol
end
close Rst
DEALLOCATE Rst
drop table tempdb..tblfkey
end
效率测试报告:开始升级UFDATA_001_2006数据库
Update_DelRepeat.sql,2011-11-24 0:51:22 -- 2011-11-24 0:51:23,0小时0分钟1秒。
升级起始时间:2011-11-24 0:51:22,结束时间:2011-11-24 0:51:25,0小时0分钟3秒。
|
|