May 1, 2008 at 1:35 pm
I've got a very strange problem with an updatable cursor not advancing.
The table has a non-clustered index on the compound Primary Key and a compound Clustered index on other fields. They share one or two fields in common.
The bit of code looks something like this:
FETCH NEXT FROM Employee_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
Update Tablename
Set Fieldname = @FieldNameValue
WHERE CURRENT OF Employee_Cursor
if @@ERROR > 0 BEGIN RETURN (SOMETHING) END
Insert into DebugTable (@FieldnameValue)
FETCH NEXT FROM Employee_Cursor
END
Now, the query that populates the cursor yields 57 rows. When this proc is run inside a transaction, the DebugTable gets thousands of inserts until the server crashes. The @FieldNameValue never moves to the next value in the cursor.
So, it looks like an infinite loop. The fix? I dropped the clustered index. The cursor then advances through all 57 iterations and finishes just dandy.
The problem is, that is such a cheese fix. I know it works, but I have no idea why. I've dropped an rebuilt the indexes/statistics over and over and get the same results.
Anyone have any ideas?
May 1, 2008 at 1:56 pm
Something isn't right, because the Return you're using there shouldn't be accepting a value. Where's the rest of the code?
I'd guess that something is causing the update to fail, which then instantiates the RETURN (breaks the loop), before hitting the next FETCH. Something is then restarting the loop, and thus the merry-go-round.
Of course - you're also not setting @fieldname in the while loop, so even IF the code got to the fetch, you'd executing the SAME update 57 times, since the @fieldname isn't changing.
please - post the valid code - it will actually help. Right now it's hard to decipher what's broken.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 1, 2008 at 1:57 pm
It would probably be good if you would give the definition of the cursor and also the indexes of the table and the datatypes of the columns reference in the indexes -- basically the cursor definition and the important pieces of the table definition.
May 1, 2008 at 2:45 pm
at least a fetch should have an into section
FETCH NEXT FROM Employee_Cursor into @colvar1,@colvar2
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 1, 2008 at 3:40 pm
I pseudocoded a lot of stuff in the example because I didn't think it would be important. I'll post the rest tonight.
May 1, 2008 at 6:16 pm
DECLARE myCursor CURSOR
LOCAL
FOR
SELECT UsgSvc.UsgSvcID, Customer.CustID, UsgSvcMultiProvision.StatusID, UsgSvc.DisconnectDate,
UsgSvcMultiProvision.ProvisioningCompanyID, UsgSvcMultiProvision.PICTypeID
FROM UsgSvc, Customer, UsgSvcMultiProvision
WHERE UsgSvc.CustID = Customer.CustID AND
UsgSvc.UsgSvcID = UsgSvcMultiProvision.UsgSvcID AND
UsgSvc.CustID = @CustID
SET @CgException=60000
SET @ErrorCode=0
SET @ErrorString=''
BEGIN
OPEN myCursor
FETCH NEXT FROM myCursor INTO @MPUsgSvcID, @MPCustID, @MPStatusID, @MPDisDate, @MPProvCoID, @MPPICTypeID
WHILE @@FETCH_STATUS = 0
BEGIN
IF @MPDisDate IS NULL OR @MPDisDate >= @DisconnectDate BEGIN
UPDATE UsgSvcMultiProvision
SET StatusID = @ProvStatusID
WHERE CURRENT OF myCursor
IF @@ERROR != 0 BEGIN
SET @ErrorString = 'Cannot update UsgSvcMultiProvision.StatusID for UsgSvcID ' + CONVERT(varchar, @MPUsgSvcID) + ' to ' + CONVERT(varchar, @ProvStatusID) + '.'
RETURN 1
END
END
FETCH NEXT FROM myCursor INTO @MPUsgSvcID, @MPCustID, @MPStatusID, @MPDisDate, @MPProvCoID, @MPPICTypeID
END
CLOSE myCursor
DEALLOCATE myCursor
END
RETURN 0
May 1, 2008 at 6:55 pm
The cursor is on a multi-table join, so I am not sure "WHERE CURRENT OF myCursor" can really be used there. I've never tried that so I don't really know, and I don't feel like testing it right now.
One thing you are not doing that would help you resolve this problem is to identify the actual error you are getting. You need to capture the value of @@error, and look at what it is. That's going to provide you more information than a 100 posts here.
May 1, 2008 at 7:57 pm
Well, I managed to fix this, but I'm still troubled by how I did it.
Ignore the code.
I dropped the clustered index and rebuilt it with a different name. Everything works fine now. This is after having tried rebuilding, defragging, integrety checking.
I'm thinking there was some corrupt stats or something that were persisting under the old name...
May 2, 2008 at 7:27 am
Get rid of the cursor !
use this instead:
update P -- I supposed you want to modify UsgSvcMultiProvision.StatusID
SET StatusID = @ProvStatusID
FROM UsgSvc U
inner join Customer C
on U.CustID = C.CustID AND
inner join UsgSvcMultiProvision P
on U.UsgSvcID = P.UsgSvcID
where U.CustID = @CustID
and ( U.DisconnectDate IS NULL
OR U.DisconnectDate >= @DisconnectDate )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply