SQL Query question

I have an odd one for everyone and hopefully someone can help me.

I have a process that selects from table 1 using a left join to Table
2. Table 2 contains the records that have already been selected and
the query is design to only select new records out of table one. THe
process then adds the records to Table 2 so the next time only new
records from table 1 are selected. THis process has been working for 2
years without fail until one day last week where the results from the
query was all the records from table 2 + the new records and resulted
in sending duplicate records.

Can anyone help me understand under what circumstances a left join
would fail? when we looked the next morning all the records were in
table 2 twice, there was no evidence that anyone had deleted the rows
and then rolled back. The process has run as designed every night
since then and the only other odd event that night is that about 2
hours before the query was run the partition ran out of space breifly.

I am at a loss to explain this and any assistance would be helpful.


Thanks

Re: SQL Query question

Pure speculation.....

From your description the process occurs in two steps. The first
selects the data from T1, the second loads it to T2. That sounds like
there is an intermediate data store. If the purge of that store
occurs after the load to T2, and the load to T2 failed, then the file
or table or whatever was not empty when the next extract from T1
occurred. If the extract from T1 appends the data it would have been
appending data that was already there from the failed run.

If the two tables must remain synchronized the extract and load should
execute within a single transaction.

Roy Harvey
Beacon Falls, CT

On Mon, 02 Jul 2007 10:15:40 -0700, serwin@gmail.com wrote:

>I have an odd one for everyone and hopefully someone can help me.
>
>I have a process that selects from table 1 using a left join to Table
>2. Table 2 contains the records that have already been selected and
>the query is design to only select new records out of table one. THe
>process then adds the records to Table 2 so the next time only new
>records from table 1 are selected. THis process has been working for 2
>years without fail until one day last week where the results from the
>query was all the records from table 2 + the new records and resulted
>in sending duplicate records.
>
>Can anyone help me understand under what circumstances a left join
>would fail? when we looked the next morning all the records were in
>table 2 twice, there was no evidence that anyone had deleted the rows
>and then rolled back. The process has run as designed every night
>since then and the only other odd event that night is that about 2
>hours before the query was run the partition ran out of space breifly.
>
>I am at a loss to explain this and any assistance would be helpful.
>
>
>Thanks

Re: SQL Query question

Roy Harvey (roy_harvey@snet.net) writes:
> Pure speculation.....
>
> From your description the process occurs in two steps. The first
> selects the data from T1, the second loads it to T2. That sounds like
> there is an intermediate data store. If the purge of that store
> occurs after the load to T2, and the load to T2 failed, then the file
> or table or whatever was not empty when the next extract from T1
> occurred. If the extract from T1 appends the data it would have been
> appending data that was already there from the failed run.
>
> If the two tables must remain synchronized the extract and load should
> execute within a single transaction.

And to add this: you must also make sure that if any errors occurs during
the transaction, that the transaction is rolled back. For many errors,
execution continues, so if you have error-checking, part of the transaction
will still be committed.

Without seeing the code, speculations is all you can get.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx