cancel
Showing results for 
Search instead for 
Did you mean: 

Problem with Alter Script generation

Former Member
0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

brackenbenavidez
Discoverer
0 Kudos

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
Active Participant
0 Kudos

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
Discoverer
0 Kudos

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
0 Kudos

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
Active Participant
0 Kudos

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
0 Kudos

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
Discoverer
0 Kudos

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
Active Participant
0 Kudos

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
Active Participant
0 Kudos

This problem has been reported under:

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

Former Member
0 Kudos

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
0 Kudos

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
0 Kudos

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
Active Participant
0 Kudos

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
Active Participant
0 Kudos

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
0 Kudos

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

Former Member
0 Kudos

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
0 Kudos

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.

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi,

Here is a short summary of my problem. Power Designer tries to rename a modified table (in my case called T2). To do that, it first adds in the script a line in order to remove the primary key of this table :

alter table T2

   drop constraint PK_T2

;

There are existing foreign keys that reference this primary key and Power Designer should remove these ones first. But it does not. These lines should be :

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_T1T2_T2')

alter table T1

   drop constraint FK_T1_T1T2_T2

;

And, of course, I got the following errors :

Msg 3725, Level 16, State 0, Line 9

The constraint 'PK_T2' is being referenced by table 'T1', foreign key constraint 'FK_T1_T1T2_T2'.

Msg 3727, Level 16, State 0, Line 9

Could not drop constraint. See previous errors.

I think that it's very close to your issue and I'm sure that the last answer does not provide us any usefull information.

So any other idea ?

Thanx

Former Member
0 Kudos

Thanks everyone for looking at this problem.  I have opened a support case with Customer Support but still no resolution.  I'm glad to know it isn't something unique to my system.  I had thought it might be related to one or more customization's I had made to the DBMS resource file.

If/when I get an answer from Customer Support I will post the results here also.

Thanks,

James Knowlton

arnaud_laurent
Active Participant
0 Kudos

Here is the expected behavior. If the code is a parent table is changed then PowerDesigner 16.5 generates an alter.sql to:

- alter the child table to drop FK constraint

- drop PK constraint of the parent table

- drop then recreate the parent table

- add the FK constraint again to the child table

Make sure you installed PowerDesigner 16.5 GA (build 3982) before applying any PowerDesigner 16.5 ESD.