1 Reply by Gordon Barker

  • Gordon Barker
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 344

Re: Report Wizard Error - duplicate key

Hi

In the process of upgrading from R2002 to R6. The database appears to have transferred successfully, but on running Report Wizard - Report about a place I get an error about a duplicate key as follows: 


Exception occurred in application Recorder 6 at 10/01/2008 10:11:26.
Version : 6.10.4.120

Exception path:
EOleException : CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is 'DSS0004600002WDV'
TExceptionPath : An error occurred executing the following SQL:
CREATE UNIQUE CLUSTERED INDEX IX_OCCURRENCE_KEY ON "#REPORT_OUTPUT" (OCCURRENCE_KEY, [TYPE])

Last event\actions: 
  actReportWizard invoked
  TfrmFilterResult created
  TfrmFilterResult activated
  TdlgWizard created

Operating System : Windows XP  5.01.2600  Service Pack 2
Physical Memory available : 1,039,724 KB

DLLs loaded:
  advapi32.dll (5.1.2600.2180)
  comctl32.dll (5.82.2900.2180)
  comdlg32.dll (6.0.2900.2180)
  gdi32.dll (5.1.2600.2180)
  HHCtrl.ocx (5.2.3790.1194)
  kernel32.dll (5.1.2600.2180)
  mpr.dll (5.1.2600.2180)
  MS5.Dll (5.0.0.12)
  MS5User.Dll (5.0.0.4)
  odbc32.dll (3.525.1117.0)
  ole32.dll (5.1.2600.2180)
  oleaut32.dll (5.1.2600.2180)
  shell32.dll (6.0.2900.2180)
  user32.dll (5.1.2600.2180)
  version.dll (5.1.2600.2180)
  winmm.dll (5.1.2600.2180)
  winspool.drv (5.1.2600.2180)

Information has been saved to the file Q:\LastError.txt

I have tried with a few different locations and those I believe to have more records have failed with this error message, with different values for the 'Most significant primary key'. The largest number of records to have succeeded so far is under 500.

Can anyone help to shed any light on this.

Thanks

Gordon

Gordon Barker
Biological Survey Data Manager
National Trust

2 Reply by Gordon Barker

  • Gordon Barker
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 344

Re: Report Wizard Error - duplicate key

Further testing has managed to produce reports with up to 4000 records, but the sites with largest numbers of records still fail at this point.

Gordon

Gordon Barker
Biological Survey Data Manager
National Trust

3 Reply by Sarah Shaw

  • Sarah Shaw
  • Veteran
  • Offline
  • Registered: 23-06-2006
  • Posts: 267

Re: Report Wizard Error - duplicate key

Hi

Just wondered whether John from Dorset Software would be able to shed any light on the above error message?

Many thanks,

Sarah Shaw
Biodiversity Information Assistant
JNCC

4 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: Report Wizard Error - duplicate key

I've just tried a Report about a Place report with about 4000 records with no problem, though its possible I am selecting different options. Gordon, any chance you could supply details on exactly what you selected in the Report Wizard?

Thanks

John van Breda
Biodiverse IT

johnvanbreda's Website

5 Reply by Gordon Barker

  • Gordon Barker
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 344

Re: Report Wizard Error - duplicate key

John

The process is Report Wizard - Report about a place - Place names - Select name, including subsites - Include taxon info - all info - new layout - Select Sample spatial reference, date, Location, Location Name, Location description, Taxon common & latin names - No sort.

The equivalent query in R2002 produces 7763 records.

There do not appear to be any duplicates in the Taxon occurence table. The taxon occurence key returned as the "Most significant primary key " does appear to relate to a record that should be selected.

Gordon

Gordon Barker
Biological Survey Data Manager
National Trust

6 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: Report Wizard Error - duplicate key

Hi Gordon,

Thanks for the information. That has indeed helped me to track it down and the fix is very simple, so it will be fixed in the next release.

Regards

John van Breda
Biodiverse IT

johnvanbreda's Website

7 Reply by Gordon Barker

  • Gordon Barker
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 344

Re: Report Wizard Error - duplicate key

Thanks John,

Any idea when the next release is likely to be? Can you suggest a workaround?

I have since found the same problem with subsites not included (parent site only, ~4400 of the 7763), but not for  just the subsites (added as individual sites, 3300). It also fails for a filtered query - Chiroptera and sub-taxa across a region (~100 properties as subsites of the region, + sub-subs etc, 1200 records)

Gordon

Gordon Barker
Biological Survey Data Manager
National Trust

8 Reply by Gordon Barker

  • Gordon Barker
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 344

Re: Report Wizard Error - duplicate key

Hi

This problem now appears to have cleared up, not sure how, but possibly as a result of IS changing permissions when sorting out another problem?

Gordon

Gordon Barker
Biological Survey Data Manager
National Trust

9 Reply by Gordon Barker

  • Gordon Barker
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 344

Re: Report Wizard Error - duplicate key

Gordon Barker wrote:

Hi

This problem now appears to have cleared up, not sure how, but possibly as a result of IS changing permissions when sorting out another problem?

Gordon

No I was wrong about that, appears to relate to the inclusion of the Observation comment field, which I'm sure is what John worked out before.

Gordon Barker
Biological Survey Data Manager
National Trust

10 Reply by Gordon Barker

  • Gordon Barker
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 344

Re: Report Wizard Error - duplicate key

Just to confirm that upgrading to 6.13 appears to have cleared up this problem.

G

Gordon Barker
Biological Survey Data Manager
National Trust