baffling delete problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

baffling delete problem

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-19 : 09:31:01

Mariam writes "I am using sql2000 to store data. I have a daily job that runs at 6 am and deletes all records older than 8 days. The stored proc that does that is:

CREATE PROCEDURE [sp_del_old] AS
SET NOCOUNT ON
declare @thedate datetime
set @thedate = dateadd(day, -8, getdate())
delete from news where newsdate < @thedate
order by newsdate desc
SET NOCOUNT OFF
GO

This has been working correctly for a long time now. Yesterday I did some some changes on the indexes. I deleted the index I had on the field "newsdate" and the index on "agency" and did a composite index on "newsdate desc, agency"
Today, the stored proc was run, but it deletes all records whose newsdate > @thedate.
I restored teh database, and run the stopred proc again, but the same result. I did a select instead of delete, and it selects the correct records. A delete does not delete the correct records.

I also changed the delete statement to
delete from news where newsdate > @thedate
And strangely enough, it deleted the old news and kept the new ones. This is wht I want, but I wouldn't keep the statement that way sicne it is not logical. Using Between did not delete the correct records either although it returns them correctly using select.

So aparently this has to do with my new index. But if so, shouldn't it affect the select also? I am really confused here. What am I doing wrong? Please help

Thank u
Mariam"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-19 : 09:58:45

I don't see anything wrong with the procedure, except the ORDER BY clause should be taken out...DELETE doesn't use an ORDER BY. I doubt that's why it's acting weird, but you never know.

I really don't see how changing the WHERE clause could possibly work. Unless the index statistics were not updated, and even then, that REALLY doesn't make sense.

Have you tried this:

DELETE FROM news WHERE DateDiff(dd, newsdate, getdate())>=8

This will avoid timestamp problems too.

lfmn
Posting Yak Master

141 Posts

Posted - 2002-02-19 : 12:25:16

I would suggest running sp_helptext on the stored procedure to make sure that your statement is correct. It's possible that the statement in the proc is not correct.

cursors are like hammers - sometimes you have to use them, but watch your thumb!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources