Problem with Alter Script generation

[Former Member]

I recently upgraded from Powerdesigner 16.0 to 16.5 and since then the generation of alter scripts for the latest changes to the data model are not running correctly.  A change to a "code" table that is referenced by many other tables fails (MS SQL Server 2008) because of the existence of a Foreign Key reference.  The tool use to generate these scripts correctly, i.e. they dropped all foreign key references and then altered the table and then recreated the foreign keys.  Not sure what has happened after the upgrade.  Does anyone else know what might have changed. 

I hope I have the correct forum, this is my first post under SAP (use to use Sybase often enough but the tool hasn't given me any trouble in a very long time).  I'll probably open a support case Monday, as this issue has only been noticed for the first time this last week but is a serious issue.  Still hoping it might be something simple that others already know.

thanks,

James K.

[brackenbenavidez · Accepted solution]

I am experiencing the same problem reported using version 16.5.2.4144 PL1.  It is identical to what has been reported by Antonin Brachet.  As it stands the product is nearly useless.  Can you please provide a work around as I am now forced to drop the FK's myself.

Thanks!

[arnaud_laurent · Accepted solution]

I did the below test with a SQLServer PDM in PD 16.5.2 PL1.

1. Create tables T1 and T2. T2 has a pk column c2.
2. Draw a reference from T1 to T2. The FK references T2.C2.
3. Archive the model
4. Rename T2 > T2new
5. Execute Apply Model Changes to Database
The generated SQL script modifies:
- Column "C2" in the Table "T2"
- Key "Key_1" in the Table "T2"
- Table "T2"
- Reference "Reference_1"

/*==============================================================*/
if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('T1') and o.name = 'FK_T1_REFERENCE_T2')
alter table T1
   drop constraint FK_T1_REFERENCE_T2
go

alter table T2
   drop constraint PK_T2
go

if exists (select 1
            from  sysobjects
           where  id = object_id('T2')
            and   type = 'U')
   drop table T2
go

create table T2NEW (
   C2                   char(10)             not null,
   constraint PK_T2NEW primary key (C2)
)
go

alter table T1
   add constraint FK_T1_REFERENCE_T2NEW foreign key (C2)
      references T2NEW (C2)
go
/*==============================================================*/

- It seems I do not reproduce what you described. Are the above scenario correct?
- What is the DBMS resource file you are using? I used MSSQLSRV2008.
- Does it happen with a new model in PD 16..5.2 PL1? Or only with a model migrated from an earlier release?

[brackenbenavidez · Accepted solution]

Arnaud,

1. "it seems I do not reproduce what you described. Are the above scenario correct?"

The scenario you describes works as expected, however using the same steps, but instead of renaming T2 add a new mandatory column to T2 and Execute Apply Model Changes to Database.  In this scenario

>>if exists (select 1

   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

   where r.fkeyid = object_id('T1') and o.name = 'FK_T1_REFERENCE_T2')

does not execute.  The key is to make a change to column that would require the table to be dropped.  For example add a mandatory column or change the datatype on a existing column that would require the table to be recreated.

2. "What is the DBMS resource file you are using? I used MSSQLSRV2008"

I tried this with MSSQLSRV2005, MSSQLSRV2008 and MSSQLSRV2012.  All yielded the same results.

3.Does it happen with a new model in PD 16..5.2 PL1? Or only with a model migrated from an earlier release?

Both new and migrated.

-Bracken

[Former Member · Accepted solution]

Hi Arnaud,

First, thank you to consider my problem

I confirm you 2 things :

1. Your scenario works fine and I obtain the same script than you from Power Designer (i.e. the drop of the foreign key is present)
2. Your scenario is different than mine. Instead of renaming the table T2, please simply a column on it.

And in my case according to this second scenario, these lines :

if exists (select 1

   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

   where r.fkeyid = object_id('T1') and o.name = 'FK_T1_REFERENCE_T2')

alter table T1

   drop constraint FK_T1_REFERENCE_T2

are not generated by Power Designer.

For your other questions :

• I target MSSQLSRV2012 DBMS but I'm sure the problem has nothing to do with this configuration;
• Yes it happens with a new model, created especially in order to reproduce/solve this problem.

[arnaud_laurent · Accepted solution]

Indeed I have reproduced it. I am sorry to say it is a bug.

Please contact SAP Technical Support to report it and request a resolution.

[Former Member · Accepted solution]

Arnaud,

First, thank you so much for investigating our issue and for your time.

Then, could tell me how to contact SAP Technical support. I'm sorry, even if we have licences since a long time, I'm totally new on this site

[brackenbenavidez · Accepted solution]

Arnaud,

Thank you for your time in reviewing this.  I have submitted a support ticket which can be located here: http://case-express.sybase.com/cx/caseSheet.do?caseNumber=11800270.

I would like to ask if it would possible to have this issue escalated?  We recently upgraded (last 6 months) to v16 and we did not purchase a support contract.  Unfortunately, SAP will not do anything to escalate this case because of the lack of a support contract.  At this time my hands are tied and we are stuck with manually building alter scripts.

Thank You,

Bracken

[arnaud_laurent · Accepted solution]

A new bug will be created to report it to engineering. But I am afraid it could not be escalated unless it is requested by a user with a support contract. I will share the reference number later today.

[arnaud_laurent · Accepted solution]

This problem has been reported under:

http://search.sybase.com/kbx/changerequests?bug_id=746449

[Former Member · Accepted solution]

Thanks.  I have sent the Bug_id # to the tech working my support case.  Hopefully they will escalate this issue and get it resolved.

[Former Member · Accepted solution]

Per my case rep:

I heard back from engineering and they have confirmed that CR has been fixed and is estimated for PD 16.5.2 EBF 2.

Hope it is out in time to help everyone.

James.

[Former Member · Accepted solution]

Hi James,

Thank you so much for this feedback. Could you tell me what "PD 16.5.2 EBF 2." means if we speak with dates ?

Regards,

[arnaud_laurent · Accepted solution]

PowerDesigner 16.5.2 EBF2 is also known as 16.5 SP02 PL02, where PL02 is the next patch level to be released around end of this month. Precise date has not been defined yet AFAIK.

I confirm its resolution.

Best Regards

[arnaud_laurent · Accepted solution]

For your information, PowerDesigner 16.5.2 PL2 has just been released and bug CR 746449 is solved. The FK constraint is now dropped.

EBF 21837 (16.5 SP02 PL02 x86) and EBF 21841 (16.5 SP02 PL02 x64) can be downloaded from http://downloads.sybase.com/

They should be published on SAP Market Place soon.

HTH

[Former Member · Accepted solution]

I have downloaded and installed EBF 21837 and it did fix the problem.

[Former Member · Accepted solution]

I recently upgraded from Powerdesigner "16.1.0.3637" to "16.1.0.3881 EBF17" and now I have exactly the same problem.

Unfortunately, EBF 21837 for 16.5 does not help me in this case.

Any kind of help is highly appreciated!

Thanks and Regards

Michael H.

[Former Member · Accepted solution]

I'd recommend upgrading to 16.5 if possible, if not then the latest EBF for 16.1.  I encountered this problem after upgrading from 16.1 to 16.5. The EBF 21837 fixed the issue in 16.5, doubt it will have any effect on 16.1.

James.