DELETE not deleting all records..mystery.

I've got a job that has 3 lines and it reports success after it runs.
DELETE FROM TABLE1 WHERE DATE >= '20070115'
DELETE FROM TABLE2 WHERE DATE >= '20070115'
DELETE FROM TABLE3 WHERE DATE >= '20070115'
I then have a separate job for each line below which imports from another
server using DTS.
SELECT * FROM TABLE1 WHERE DATE >= '20070115'
SELECT * FROM TABLE2 WHERE DATE >= '20070115'
SELECT * FROM TABLE3 WHERE DATE >= '20070115'
The 1st job fails, but the other 2 are ok. I then use QA and execute the
single statement:
DELETE FROM TABLE1 WHERE DATE >= '20070115'
and several thousand records are deleted. huh?
During this process, there is no application or person inserting records
into the table.
How can there still be records in the table after the delete job ran? I then
rerun the job to insert from another server and it succeeds.
This has happened on many servers over the last few months. Any opinions
greatly appreciated.
Thanks,
Don
SQL 2000
Hi
"donsql22222" wrote:

> I've got a job that has 3 lines and it reports success after it runs.
> DELETE FROM TABLE1 WHERE DATE >= '20070115'
> DELETE FROM TABLE2 WHERE DATE >= '20070115'
> DELETE FROM TABLE3 WHERE DATE >= '20070115'
> I then have a separate job for each line below which imports from another
> server using DTS.
> SELECT * FROM TABLE1 WHERE DATE >= '20070115'
> SELECT * FROM TABLE2 WHERE DATE >= '20070115'
> SELECT * FROM TABLE3 WHERE DATE >= '20070115'
> The 1st job fails, but the other 2 are ok. I then use QA and execute the
> single statement:
> DELETE FROM TABLE1 WHERE DATE >= '20070115'
> and several thousand records are deleted. huh?
> During this process, there is no application or person inserting records
> into the table.
> How can there still be records in the table after the delete job ran? I then
> rerun the job to insert from another server and it succeeds.
> This has happened on many servers over the last few months. Any opinions
> greatly appreciated.
> Thanks,
> Don
> SQL 2000
It would appear that when you say the first job to import data fails it is
not failing before data has been committed. If the package contains more than
a single Execute SQL task then it could be one of the subsequent steps that
fails (assuming they are not in a single transaction) or if the job has more
than one step it could be a subsequent step that has failed or the step has
already committed the data before it fails.
John