1 Reply by Giffdj

  • Giffdj
  • Veteran
  • Offline
  • From: KMBRC
  • Registered: 02-06-2008
  • Posts: 72

Re: Batch Delete - Del 3, Events with no Samples

Hi,

Trying to run Batch Delete Del 3, Events with no Samples and I’m getting the following error:

(0 row(s) affected)
Msg 547, Level 16, State 0, Line 31
The DELETE statement conflicted with the REFERENCE constraint "FK_SAMPLE_RECORDER_SURVEY_EVENT_RECORDER". The conflict occurred in database "nbndata", table "dbo.SAMPLE_RECORDER", column 'SE_RECORDER_KEY'.
The statement has been terminated.

(0 row(s) affected)
Msg 547, Level 16, State 0, Line 49
The DELETE statement conflicted with the REFERENCE constraint "FK_SURVEY_EVENT_RECORDER_SURVEY_EVENT". The conflict occurred in database "nbndata", table "dbo.SURVEY_EVENT_RECORDER", column 'SURVEY_EVENT_KEY'.
The statement has been terminated.

(1 row(s) affected)

Some of the Events in question have no Event Recorder so that might be the problem, but any help would be welcome.

Cheers, Dave

2 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,645

Re: Batch Delete - Del 3, Events with no Samples

This suggest a problem with the data.  I  don't know any way that Recorder past or present can create this situation, but there may have been a way once. Otherwise it has must have been someone making changes directly through the tables,

I have uploaded an XML Report  JNCC_sy08_Problem_SER.zip to the forum upload area. This will detect the problems. Not yet sure how to fix, but it will help to know the extent.

Mike Weideli

MikeWeideli's Website

3 Reply by Giffdj

  • Giffdj
  • Veteran
  • Offline
  • From: KMBRC
  • Registered: 02-06-2008
  • Posts: 72

Re: Batch Delete - Del 3, Events with no Samples

Mike,

Thanks for the program, here are the results:

Survey_Event_key    Sample_Recorder_Sample_Key
SR0002650000133I    SR000265000004CE
SR0002650000004R    SR00026500000059
SR0002650000004R    SR00026500000059
SR00014500001I8P    SR00014500001OMM
SR0002650000130E    SR000265000000KO
SR0001450000000P    SR0001450000000Z
SR000145000003AW    SR000145000004ES
SR000145000003AW    SR000145000004ES
SR00028300000PGQ    SR00028300000QTG
SR00014500001I8P    SR00014500001OMN
SR000265000000DJ    SR000265000000F4
SR00026500001333    SR000265000004BO
SR0002650000003W    SR0002650000004M
   

I was expecting more as the batch delete said 1247 records affected.
I should probably have mentioned that, as the batch program didn’t give a helpful error message, I was running just the SQL part via Studio Express

Cheers, Dave

4 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,645

Re: Batch Delete - Del 3, Events with no Samples

This has proved a bit troublesome to sort out, because the situation which exists just shoudn't be possible. The following SQL should sort out the problem and allow the Batch Update to run. As always please   take a back up before running.  In your case the query should affect 13 records.


CREATE TABLE Temp# (SERKey char(16) COLLATE SQL_Latin1_General_CP1_CI_AS , SKey char(16)COLLATE SQL_Latin1_General_CP1_CI_AS  )

INSERT INTO temp#
SELECT SR.SE_Recorder_Key, SR.Sample_Key FROM Survey_Event SE
INNER JOIN Survey_Event_Recorder SER ON SER.Survey_Event_Key = SE.Survey_Event_Key
INNER JOIN Sample_recorder SR ON SR.SE_Recorder_Key = SER.SE_Recorder_Key

WHERE
NOT EXISTS (SELECT * /**/FROM/**/ Sample S WHERE S.sample_key = SR.Sample_Key and S.Survey_Event_Key = SE.Survey_Event_Key)


DELETE FROM SR
FROM 
Sample_recorder SR INNER JOIN TEMP# ON TEMP#.SKey = SR.Sample_Key AND temp#.SERKey=SR.SE_Recorder_Key

DROP TABLE TEMP#

Mike Weideli

MikeWeideli's Website

5 Reply by Giffdj

  • Giffdj
  • Veteran
  • Offline
  • From: KMBRC
  • Registered: 02-06-2008
  • Posts: 72

Re: Batch Delete - Del 3, Events with no Samples

Thanks Mike,

I'll run the fix and the batch delete in the Test System first.

Will try and do it today and let you know the outcome.

Thanks for your help.

Cheers,
Dave

6 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,645

Re: Batch Delete - Del 3, Events with no Samples

Dave

I have fixed the Batch Update to cope with this situation. Will be better if you try this.

http://forums.nbn.org.uk/uploads.php?file=JNCCDel3SENoSamp.xml

Mike

Mike Weideli

MikeWeideli's Website

7 Reply by Giffdj

  • Giffdj
  • Veteran
  • Offline
  • From: KMBRC
  • Registered: 02-06-2008
  • Posts: 72

Re: Batch Delete - Del 3, Events with no Samples

Mike.

Ran the new version of Del 3, ran with no problems. Ran your program Sy08 to check the data and it still displayed 5 of the 13 records in error. Ran your SQL script and it affected 5 rows. Re-ran Sy08 and displayed no records.
On this basis I am assuming that I should run the SQL fix, then the new Del 3 to achieve the best result. I will restore the Test system and give it a go.

Cheers, Dave

8 Reply by Giffdj

  • Giffdj
  • Veteran
  • Offline
  • From: KMBRC
  • Registered: 02-06-2008
  • Posts: 72

Re: Batch Delete - Del 3, Events with no Samples

Hi Mike,

The fix sorts out the 13 rows, and then the Batch Delete runs OK, although doesn't tell you what it's done.

Cheers, Dave