I have a series of delete statements that basically is a maintenance script that deletes out records older than 90 days. The error I get is The DELETE statement conflicted with the REFERENCE constraint "FK1_0126"
This scipt is run through the sql agent on a 2005 sql server. It runs nightly and in most cases it fails. Occassionally it will work. The script is below. What is puzzling is if I run the script manually through Management studio as the same user, it will always be successful. Also, I have this same script running on a sql2000 environment and it has never failed. What am I missing? Would some sort of table locking cause a reference constraint? The setup of the Job in the two sql servers agent appear to be exactly the same.


delete from failedxmlbody where importbtchstatid in (select importbtchstatid
from importbtchstat where begindtm < dateadd(dd,-90,getdate()))
go
delete from failedxmldata where importbtchstatid in (select importbtchstatid
from importbtchstat where begindtm < dateadd(dd,-90,getdate()))
go
delete from importctntstat where importbtchstatid in (select importbtchstatid
from importbtchstat where begindtm < dateadd(dd,-90,getdate()))
go
delete from importresult where importbtchstatid in (select importbtchstatid
from importbtchstat where begindtm < dateadd(dd,-90,getdate()))
go
delete from importbtchstat where begindtm < dateadd(dd,-90,getdate())
go
delete from importcontent where importbatchid not in (select distinct importbatchId from importbtchstat)
go
delete from importbatch where importbatchid not in (select distinct importbatchId from importbtchstat)
go