Powerdesign 16.0 generated SQL Server2000 database script when ms_description does not exist solve the problem

According to the information on the Internet, found a solution, the original source: http://www.cnblogs.com/24tt/p/5047257.html

Powerdesign 16.0 generated script statement, inside Sql2000, the statement with the field notes executed with the following error:

PowerDesigner cannot update or delete properties. The attribute ms_description of "xxx" does not exist.

Mssqlsrv2000::script\objects\column\columncomment

Modified Column colcomment

Modify Column columncomment template path is Database, Edit current DBMS Form General tab under Script, Objects, C Olumncomment

In fact, the default build statement has a judgment error: The default is the following ():

[If exists (select 1 from sysproperties where id = object_id (' [%qualifier%]%table% ') and type = 4) begin

The above judgment is whether the table has a note, when there are 2 and more comments to add (such as C1,C2), add to the second note C2, the judgment system must exist, and immediately after the need to perform the deletion C2, because C2 have not been added need to delete will definitely prompt " unable to update or delete properties ". Sp_dropextendedproperty

So you need to modify the judgment, to locate the exact field has a comment, then delete, modify the following ( red for the added part, SQL2000 test pass):

[If Exists (select 1

From sysproperties where id = object_id (' [%qualifier%]%table% ') and smallid in (select Colid From syscolumns WHERE id = object_id (' [%qualifier%]%table% ') and name =%.q:column% and number = 0) and Name= ' ms_description ' and type = 4) begin

Finally, the complete code is posted:

[if exists (select 1from syspropertieswhere id = object_id (' [%qualifier%]%TABLE%') and Smallid in (select Colidfrom syscolumnswhere id = object_id (' [%qualifier%]%TABLE%') and name =%.q:column% Andnumber = 0) and name='Ms_description'and type = 4) begin [%owner%?[. O:[execute][exec]]sp_dropextendedproperty [%r%?[ N]]'Ms_description', [%r%?[ N]]'User', [%r%?[ n]]%.q:owner%, [%r%?[ N]]'Table', [%r%?[ N]]%.q:table%, [%r%?[ N]]'column', [%r%?[ n]]%.q:column%:d eclare @CurrentUser sysnameselect @CurrentUser = user_name () [. O:[execute][exec]]sp_dropextendedproperty [%r%?[ N]]'Ms_description', [%r%?[ N]]'User', [%r%?[ N]] @CurrentUser, [%r%?[ N]]'Table', [%r%?[ N]]%.q:table%, [%r%?[ N]]'column', [%r%?[ n]]%.q:column%]end][%owner%?[. O:[execute][exec]]sp_addextendedproperty [%r%?[ N]]'Ms_description', [%r%?[ n]]%.q:comment%, [%r%?[ N]]'User', [%r%?[ n]]%.q:owner%, [%r%?[ N]]'Table', [%r%?[ N]]%.q:table%, [%r%?[ N]]'column', [%r%?[ N]]%.q:column%:select @CurrentUser = user_name () [. O:[execute][exec]]sp_addextendedproperty [%r%?[ N]]'Ms_description', [%r%?[ n]]%.q:comment%, [%r%?[ N]]'User', [%r%?[ N]] @CurrentUser, [%r%?[ N]]'Table', [%r%?[ N]]%.q:table%, [%r%?[ N]]'column', [%r%?[ n]]%.q:column%]

This code has been tested and is not a problem for the time being.

This is the modified location

Powerdesign 16.0 generated SQL Server2000 database script when ms_description does not exist solve the problem