Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old November 21st, 2013, 12:02 PM
Authorized User
Points: 153, Level: 3
Points: 153, Level: 3 Points: 153, Level: 3 Points: 153, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2004
Location: , , .
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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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 09:06 AM
problem with delete statement thas123 SQL Server 2000 6 March 23rd, 2006 12: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 12:08 PM
sql delete statement sand133 VB Databases Basics 2 July 7th, 2004 10:56 PM



All times are GMT -4. The time now is 08:10 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.