Hello,

We have a DTS package that we are testing some issues against. It
seems that SQL that is executed in DTS (against server 7.0 SP4) causes
excessive locking at the row level. Reading up on transaction
isolation levels, the default mode of "read committed" should be just
fine, and not cause this blocking. I was able to fire up a query using
Query Analyzer, after setting 'read committed'. The query took 40
seconds. With another connection, I was able to perform an update to
the underlying table with no blocking.

When I run the same exact query in a DTS SQL task, even after
explicitly setting 'read committed', I am unable to update the same row
until the query completes. For a system with 500 or so users, this
causes serious issues.

When I performed a trace against the two scenarios, it seems that the
SQL statement that gets executed from DTS displays a ton of
Lock:Acquired / Lock:Released. Thousands! There are 80,000 rows in
this table, so it's almost what appears to be row level locking.

The trace against the same query from Query Analyzer shows none of
this. It shows a handful of Lock:Acquired / Released, but more like a
dozen or so.

Does anyone know of what might be causing this? I'm wondering if even
temporary tables will help if the original query will ALWAYS lock the
underlying table from updates.

SEtting read UNcommitted seems to work, but we must have committed data
only in this package.

Best regards,
-Sean

I just confirmed that this also occurrs on SQL Server 2000, SP3. This
just doesn't make sense. Why is this locking / blocking occurring
inside DTS, but not when I execute a query through Query Analyzer?

-Sean

Even more information.. performing a 'sp_lock' against the server while
the query is running from DTS I can quite visibly see many page locks!
I'm assuming that page locks cause 'read committed' transactions to
block...

This is frustrating. I really don't want to resort to 'read
uncommitted'

Ack!
-Sean