Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  CTXPACKET (Updated)

Author  Topic  Ivan Starting Member 3 Posts Posted - 2001-07-26 : 17:01:34 Trying to interpret information from the SQL Server 2000 Enterprise Manager Process Info Screen. My process has generated serveral subprocesses of which two are interminably in wait status with a wait type of CTXPACKET. What does this mean? I can't find anything in the documentation. I know I have a deadlock, but what is it that Enterprise Manager is reporting? The stored procedure which generated this process attempts to UPDATE a table using nested SELECTs on the same table, which I suppose is the type of thing that tends produce deadlocks. What's funny is that although I am running this query on a reporting database containing static data, on which I am the only user, sometimes the stored procedure executes sucessfully (in 5 min) and other times it hangs (the source data is refreshed so the underlying table contains exactly the same rows each time). The table contains employment status data, each record representing a single month for a single employee. For some employees we are missing information for certain months and my stored procedure attempts to fill in these gaps by using the first preceding month in which that employee has information on file. For instance there are 8 records for employee John Doe in tbl144_Eligibility: RecID SSN Start Date EmployerPartFull 1 123-45-6789 01/01/2001 F 2 123-45-6789 02/01/2001 F 3 123-45-6789 03/01/2001 NULL 4 123-45-6789 04/01/2001 NULL 5 123-45-6789 05/01/2001 P 6 123-45-6789 06/01/2001 NULL 7 123-45-6789 07/01/2001 P 8 123-45-6789 08/01/2001 P The query should update the gaps for March and April (RecID 3 & 4) with the status ("F")from the last month before March for which we had information, February; June (Rec ID 6) should be updated with the status ("P") from the last month before June which we had infomation, May. Here is the query (pardon the abysmal formatting): UPDATE E SET E.EmployerPartFull = (SELECT E2.EmployerPartFull FROM tbl144_Eligibility WHERE E2.SSN = E.SSN AND E2.StartDate = (SELECT MAX(E3.StartDate) FROM tbl144_Eligibility E3 WHERE E3.SSN = E2.SSN AND E3.EmployerPartFull IS NOT NULL AND E3.StartDate < E.StartDate)) FROM tbl144_Eligibility E WHERE E.EmployerPartFull IS NULL I can't get this to run reliably even when I add SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. Can anyone suggest an approach that might work? Ivan