PD16 Generate Datebase For Sql2008R2时报脚本错误“对象名sysproperties无效”

2024-06-14 04:04| 来源: 网络整理| 查看: 265

PowerDesinger16创建数据库表到SQL2008R2时，执行报“对象名sysproperties无效”错误。

主要是在建模时我们对表、列增加了些说明注释，而Sql2005之后系统表sysproperties已废弃删除而改用sys.extended_properties所致。

此问题解决主要参考了http://hi.baidu.com/xuefliang/item/45e7f71421d5a67871d5e8e2，

1、修改Table TableComment模板

路径是 Database -> Edit Current DBMS 窗体 General 选项卡下 Script -> Objects -> Table –> TableComment

[if exists (select 1 from sys.extended_properties where major_id = object_id('[%QUALIFIER%]%TABLE%') and minor_id = 0 ) /* SQL2008 属性表sysproperties改为 sys.extended_properties代替，替换以下脚本 [if exists (select 1 from sysproperties where id = object_id('[%QUALIFIER%]%TABLE%') and type = 3) */

2、修改Column ColumnComment模板

路径是 Database -> Edit Current DBMS 窗体 General 选项卡下 Script -> Objects -> Column –> ColumnComment

[if exists (select 1 from sys.extended_properties where major_id = object_id('[%QUALIFIER%]%TABLE%') and minor_id 0 and name = 'MS_Description') /* SQL2008 属性表sysproperties改为 sys.extended_properties代替，替换以下脚本 if exists (select 1 from sysproperties where id = object_id('[%QUALIFIER%]%TABLE%') and type = 4) */

也可创建sysproperties视图来，通过此视图处理以上问题。

if exists (select 1 from sysobjects where name = 'sysproperties' and xtype = 'V') begin DROP VIEW sysproperties end GO CREATE VIEW sysproperties AS SELECT A.name As TableName,A.id As TableID,B.Name As ColName,B.colid As ColID,B.xtype As ColType,C.name As PropName,C.Value As PropValue FROM sysobjects As A INNER JOIN syscolumns As B ON A.id = B.id INNER JOIN sys.extended_properties As C ON C.major_id = A.id AND ( minor_id = B.colid)

【本文地址】

公司简介

联系我们