on 07-14-2013 7:53 PM
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.
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
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
Hi Arnaud,
First, thank you to consider my problem
I confirm you 2 things :
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 :
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
This problem has been reported under:
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.