CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqldatabase-cursor
M.R.
M.R.

Reputation: 4827

sql server cursor stuck in loop

Ok, I'm totally at a loss - this bit of code used to work, and now suddenly doesn't....

    declare GetAllCodes cursor local for 
    select ac.activationCodePKID from ActivationCodes ac
    left join OrdersLineItems od on ac.activationCodePKID = od.activationCodePKID 
    where od.activationCodePKID is null and ac.internalorderPKID is not null
    and ac.campaignID is not null
    and ac.enteredBy like 'ProcessRequest|Entitlement%'

    RAISERROR ('step completed', 0, 1) WITH NOWAIT 


     open GetAllCodes 
           while (1=1)
           begin             

             RAISERROR ('entering cursor', 0, 1) WITH NOWAIT 

                  fetch next from GetAllCodes into @activationCodePKID

                  if (@@fetch_status <> 0)
                  begin

                         DEALLOCATE GetAllCodes 
                         break

                  end


             exec fixOrder @activationCodePKID, 6, 7

          end

It seems to just get stuck in a loop...I've commented out the exec statement, and just put in a print statment, but it just keeps going. The 'step completed' print statement gets printed, and so does the 'entering cursor' statement. And then nothing.... just hangs. The query itself returns 192 rows, so to just loop over, it should loop over 192 times and then break out and end. Ideas?

EDIT:

I added this:


declare @var varchar(10)
set @var = 'here: ' + cast(@@fetch_status as varchar(10))
RAISERROR (@var, 0, 1) WITH NOWAIT

...right after the fetch next from GetAllCodes into @activationCodePKID statement - still nothing. The 'entering cursor' still gets printed, but then it just hangs...

EDIT 2:

I stripped out a lot of stuff, and added this right after the 'declare cursor' statement, to see if I can output ANYTHING...


RAISERROR ('query done', 0, 1) WITH NOWAIT 
open GetAllCodes 
fetch next from GetAllCodes into @activationCodePKID  
close GetAllCodes 
deallocate GetAllCodes

Still hangs... so then I took out the 'fetch statement', and it seemed to not hang anymore. It didn't do anything, obviously, because I don't have it do anything, but it completed execution.

Which makes me think as to why the fetch statement is hanging. Is there some server setting that could impact this? Some memory issues? Hardware issues?

Upvotes: 0

Views: 6112

Answers (2)

DaveShaw
DaveShaw

Reputation: 52798

Is there a reason the cursor is structured like that?

Usually cursors are built like so:

declare @dbName varchar(max);
declare myCursor cursor for select [name] from sys.databases;
open myCursor;
fetch next from myCursor into @dbName;

while (@@fetch_status = 0)
begin
  print @dbName;
  fetch next from myCursor into @dbName;
end;

close myCursor;
deallocate myCursor;

Whenever they get stuck in a look will be because the fetch status isn't been set to zero (usual cause for me, is missing the "fetch next").

--

EDIT: Can you check in the Activity monitor and see if that piece of SQL is running? Is there a deadlock?

Upvotes: 4

Brad Divine
Brad Divine

Reputation: 354

My guess is, @@fetch_status is returning something other than 0, e.g. -1 or -2

MSDN Link

Maybe check to see what exactly is in there; that should give you a clue about what is wrong.

Upvotes: 0

Related Questions