Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
|
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 21st, 2013, 01:02 PM
Authorized User
 
Join Date: Aug 2004
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default delete statement conflicts with a reference constraint

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Reference Constraint? jn148 BOOK: Beginning ASP.NET 3.5 : in C# and VB BOOK ISBN: 978-0-470-18759-3 4 December 29th, 2008 10:06 AM
problem with delete statement thas123 SQL Server 2000 6 March 23rd, 2006 01:13 PM
Delete Statement Error Jane SQL Language 1 October 21st, 2005 01:45 PM
T-SQL Delete statement Alcapone C# 3 November 3rd, 2004 01:08 PM
sql delete statement sand133 VB Databases Basics 2 July 7th, 2004 10:56 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.