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

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.