DELETE doesn't remove all records

[eichmat]

I have a stored procedure that is to delete all records for an order. The delete statement does not fail, but doesn't remove all of the records I expect it to remove. The statement in the SP is literally:

DELETE
FROM Order_Picked_Items
WHERE (Site = @m_site)
AND (Order_Num = @m_order_number)

The PK on the table is Site, Order_Num and a Line_Num and is non-clustered.

The table has a delete trigger that copies the deleted records to an audit table in another database, but nothing else "strange".

Server Info:
- SQL 2000 (no service packs @@VERSION: 8.00.194)
- Windows NT 4.0 (SP 6)

Lately, with orders with 150+ line-items, only 1 or 2 records are removed by this statement... Anyone know why SQL would not remove all fo the records?

Thanks in advance...

 

[NoCoolHandle]

Foreign key constraints?

 

[eichmat]

Sorry--forgot to mention those:

Only one FK back to the Orders table on Site and Order_Num.

 

[SQLSister]

Do you have cascading delete turned for the foriegn key? It won't delete these records without it unless you delte the foreign key records first. Also, whre are hte variables coming from and what do you expect to be in them? If you are populating the variables in a cursor, only the value from the last record processed will be inthere which could lead to few items than you thought if you really need multiple values for the variables.

You could do a select with the same where conditions inthe stored procedure first as a debug test to see if the results you want are returned with the where clause as written. if you are returning the recoords and they are not deleting, I would definitly suspect the foreign key. In this case turn on cascade delete or write a delte statemetn to delte the foreign key records first if you have a good reason to leave cascade delete off.

If the select does not return as many records as you think it should, then your where clause is not right.

 

[SQLBill]

I would like to suggest that you quickly upgrade to Service Pack 3. There is a worm (SLAMMER WORM) that you are vulnerable to (along with other issues that the patch solves).

-SQLBill