P4 to SQL error

[Verticalgambit]

I am having an issue with converting from P4 to SQL (development database). During the conversion of page/historical files (cm3tpage, cm3rpage, devicehistory) I am getting an error (see example below):

2292 01/28/2008 13:24:12 Error: SQL State: 23000-2627 Message: [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of UNIQUE KEY constraint 'cm3tpagem1_P'. Cannot insert duplicate key in object 'cm3tpagem1'.
2292 01/28/2008 13:24:12 Row Number = 1, Column number = -1
2292 01/28/2008 13:24:12 Error: SQL State: 01000-3621 Message: [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
2292 01/28/2008 13:24:12 Row Number = 1, Column number = -1
2292 01/28/2008 13:24:12 sqlExec error statement= INSERT INTO cm3tpagem1 (number,page,total_pages,category,status,approval_ status,requested_by,request_dept,request_phone,req uest_date,assigned_to,assign_dept,assign_phone,ass ign_date,coordinator,coord_dept,coord_phone,coord_ date,planned_start,system,key_item_affected,planne d_end,reason,duration,current_phase,risk_assessmen t,ipl_required,location_code,priority,ipl_type,dat e_entered,operator,lastfldc,openfldc,resolved_prob lems,prereq_tasks,coreq_tasks,alert_stage,orig_dat e_entered,orig_operator,parent_change,business_are a,backout_duration,sysmodcount,sysmoduser,sysmodti me) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
2292 01/28/2008 13:24:12 dbInsert: The record being added contains a duplicate key
2292 01/28/2008 13:24:12 dbInsert: file:(TEMPcm3tpage) key:(header,number=1222,header,page=1)


Basically the mapping for the file cm3tpage has "number" and "page" as a unique key. So number=1222 and page=1, number=1222 and page=2 is unique. SQL is reading this as the individual files are unique. The first number=1222 and page=1 is ok, but the second number=1222 and page=2 gives and error, because Number=1222 is already in the system.

Has anyone run into this problem? What is the best way to fix it. I can just tell the mapping that there are no unique keys and have SQL DB admin make the two file unique on the db side. Is that the correct way to handle the problem?

FYI, my intent is to go completely to SQL and not use the P4 DB after the conversion.

Thanks in Advance

JP

[tommy]

What version of SC ?

It may be a bug with the MS SQL implementation. I have not seen this error with Oracle (on 5.1 or 6.x).

[Verticalgambit]

What version of SC ?

It may be a bug with the MS SQL implementation. I have not seen this error with Oracle (on 5.1 or 6.x).

I am in SC 6.1.1