Un-Expected duplicate records from a SELECT stmt 
Author Message
 Un-Expected duplicate records from a SELECT stmt

Hi!

I encountered a strange behaviour in one of my SELECT stmts
inside a DTS package. To be more precise, I'll give an
example data.

ORDER Table:

orderkey     status

001          Picked

ORDERDETAIL Table:

orderkey     lineno    item

001          01        1A

statement:

SELECT o.orderkey, o.status

FROM orders o (nolock) join orderdetail od (nolock)

ON o.orderkey = od.orderkey

Now, here how I encountered the problem. The SELECT
statement executes within a DTS package. While it was
executing during one of its scheduled run, a user updated
the ORDERS table and changed the status field to "Shipped".
After checking the result of the DTS (it's producing a text
file), 2 records were extracted:

orderkey    status

001         Picked

001         Shipped

The SELECT stmt in the DTS package is only executed once
and result is directly written into a text file. My dilemma
is, how come it retrieved 2 records?!? Very strange
behaviour...

Thanks alot in advanced. Your input will be greatly
appreciated...

Wally



Tue, 27 Sep 2005 15:38:44 GMT
 Un-Expected duplicate records from a SELECT stmt

That is behaviour that is probably possible if you use the nolock hint.
Apparently the SELECT got both the before and the after image of the update
that happened. Because nolock doesn't honour locks it is able to read this
non-committed data. Just remove the nolock hint and use the default locking
instead and the problem will disappear.

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.


Quote:
> Hi!

> I encountered a strange behaviour in one of my SELECT stmts
> inside a DTS package. To be more precise, I'll give an
> example data.

> ORDER Table:

> orderkey     status

> 001          Picked

> ORDERDETAIL Table:

> orderkey     lineno    item

> 001          01        1A

> statement:

> SELECT o.orderkey, o.status

> FROM orders o (nolock) join orderdetail od (nolock)

> ON o.orderkey = od.orderkey

> Now, here how I encountered the problem. The SELECT
> statement executes within a DTS package. While it was
> executing during one of its scheduled run, a user updated
> the ORDERS table and changed the status field to "Shipped".
> After checking the result of the DTS (it's producing a text
> file), 2 records were extracted:

> orderkey    status

> 001         Picked

> 001         Shipped

> The SELECT stmt in the DTS package is only executed once
> and result is directly written into a text file. My dilemma
> is, how come it retrieved 2 records?!? Very strange
> behaviour...

> Thanks alot in advanced. Your input will be greatly
> appreciated...

> Wally



Tue, 27 Sep 2005 17:12:53 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Problem w/CASE stmt & many duplicate records

2. correction :select duplicate and non duplicate record

3. select duplicate and non duplicate records

4. SQLExec stmt - field limit in select stmt with multiple tables

5. SELECT stmt to count records

6. I am getting duplicate records, I mean everything is duplicate even Access record number

7. No. of rows selected by a SELECT stmt

8. Select statement getting duplicate records

9. select duplicated records only

10. Select Duplicate Records?

11. Duplicate Records How to Delete/Select Into?

12. help with nested/derived select [filtering duplicate records]


 
Powered by phpBB® Forum Software