Hi there:

I developed a DTS package some time ago and the user reported that the
package wasn't working anymore. The DTS uses 2 stored procedures
separately to do inserts on a particular table, and when I tried to
open that table to read it ('open table' in Enterprise Manager (this
gives me a timeout error) or via a simple query in Query Analyzer(just
hangs)) it hangs. The DTS would have been writing about 25000 records
a day, and deleting records older than 11 days. So the table is not
incredibly large. The table was only being accessed by one report run
once daily, so it's not like there was a lot of user access on this
table, and it was only being updated by this DTS package. I can open
and read other tables in the same database

Not a lot to go on here yet, I'm limited on thinking time due to other
commitments. I can post the whole code to the DTS step that updates
it, a snipet is below. Right now I'm mainly interested in knowing how
to get information on what state the table is in that it is hanging
i.e. is it locked somehow or corrupt. Any system stored procedures I
could run?

Thanks,
Kayda

DECLARE Parse_Cleaned_CBC_Data CURSOR

FOR

SELECT * FROM cTable
WHERE LEFT(CallEventName,2) <> 'AN'
ORDER BY Site, CallID, [Timestamp]

OPEN Parse_Cleaned_CBC_Data
/* Loop through the cursor*/
FETCH NEXT FROM Parse_Cleaned_CBC_Data INTO
@SiteName, @CallID, @Timestamp, @CEvtName, @LoginID, @AssData, @Dest,
@Source
WHILE @@FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM Parse_Cleaned_CBC_Data INTO
@SiteName, @CallID, @Timestamp, @CEvtName, @LoginID, @AssData, @Dest,
@Source

END
CLOSE Parse_Cleaned_CBC_Data
DEALLOCATE Parse_Cleaned_CBC_Data

Check locks to see if something is blocking the package from completing.

--
Simon Worth


"Kayda" <tabladude (AT) gmail (DOT) com> wrote

If EM is hung up then either the resources are shot are your process is
blocking. Run sp_who2 and look in the blkby attribute

Allan

"Kayda" <tabladude (AT) gmail (DOT) com> wrote