Cursor fetch loops endlessly

Here is some test code.

I am afraid you won't find any problem on the test code, as I could not do so myself. It occurs only in my production database.

Seems that the general consent is that there couldn't be anything wrong really in SQL Server itself? Has no-one every seen a cursor behaving this way?

If I can rule this out as a possibility, then well, alright, I'll have to dig into the rest of this application. I just want to know if this is a possibility.

The application I am having here is a mix of Visual Basic with some direct SQL queries and a handful of stored procedures. The cursor I am talking about is in a stored procedure. This isn't so pretty obviously.

I am not sure if Visual Basic isn't doing cursor fetching behind the scenes so this could mess up my cursor possibly also.

-- test database and tables --

-- create new test data base

create database tmp4051

go

--

use tmp4051

go

IF OBJECT_ID('dbo.Addresses','U') IS NOT NULL

DROP TABLE Addresses

IF OBJECT_ID('dbo.lpn','U') IS NOT NULL

DROP TABLE lpn

create table Addresses (AccountID int PRIMARY KEY,

Name1 varchar(50),

Name2 varchar(50),

Address1 varchar(50),

country char(2),

lpn int NULL)

create table lpn (deliveryID int,

lpn int IDENTITY(1,1))

-- test data --

insert into Addresses (AccountID, Name1, Name2, Address1, country)

select 1, 'Blow', 'Joe', 'Smithson Street 3540', 'HR' union all

select 2, 'Blow', 'Joe', 'Smithson Street 3540', 'HR' union all

select 3, 'Blow', 'Joe', 'Smithson Street 3540', 'D' union all

select 4, 'Blow', 'Joe', 'Smithson Street 3540', 'D' union all

select 5, 'Blow', 'Joe', 'Smithson Street 3540', 'D' union all

select 6, 'Blow', 'Joe', 'Smithson Street 3540', 'HR' union all

select 7, 'Blow', 'Joe', 'Smithson Street 3540', 'F' union all

select 8, 'Blow', 'Joe', 'Smithson Street 3540', 'F' union all

select 9, 'Blow', 'Joe', 'Smithson Street 3540', 'F' union all

select 10, 'Blow', 'Joe', 'Smithson Street 3540', 'HR' union all

select 11, 'Blow', 'Joe', 'Smithson Street 3540', 'HR' union all

select 12, 'Blow', 'Joe', 'Smithson Street 3540', 'HR' union all

select 13, 'Blow', 'Joe', 'Smithson Street 3540', 'HR' union all

select 14, 'Blow', 'Joe', 'Smithson Street 3540', 'HR' union all

select 15, 'Blow', 'Joe', 'Smithson Street 3540', 'A' union all

select 16, 'Blow', 'Joe', 'Smithson Street 3540', 'A' union all

select 17, 'Blow', 'Joe', 'Smithson Street 3540', 'US' union all

select 18, 'Blow', 'Joe', 'Smithson Street 3540', 'HR' union all

select 19, 'Blow', 'Joe', 'Smithson Street 3540', 'GB' union all

select 20, 'Blow', 'Joe', 'Smithson Street 3540', 'GB' union all

select 21, 'Blow', 'Joe', 'Smithson Street 3540', 'GB'

-- the offending code is here: ---------------

declare @newLPN int;

declare @currentID int;

set @newLPN = 0;

set @currentID = 0;

DECLARE cursorlpn CURSOR FOR

select AccountID from Addresses

where country = 'HR';

open cursorlpn

fetch next from cursorlpn into @currentID

while (@@fetch_status = 0)

begin

insert into lpn (deliveryID) values (0);

set @newLPN = scope_identity();

update Addresses set lpn = @newLPN where AccountID = @currentID;

fetch next from cursorlpn into @currentID

end

close cursorlpn

deallocate cursorlpn

I can't reproduce the scenario you described.

Based on your sample data the code runs as expected (I'm purposely not saying it runs "fine" 😉 ).

If you already tried several filters (including LOCAL STATIC FORWARD_ONLY) and you still don't want to rewrite it as a set-based solution, you could try to narrow-down the root-cause by assigning a NEWID() to the outer sproc and include that column into the lpn table. Therewith you'd be able to verify that the sproc itself is going crazy or if it's called multiple times.

My question reagrding the process in general:

Why isn't there any WHERE condition to ensure that only rows without an already assigned lpn value will be selected?

What I've seen when runnig your code: If you call it twice, then it will update all rows with country = 'HR' even though those rows already have an lpn value assigned. Is that intentionally?

Based on a "gut" feeling and previous experience with "run away updates", try replacing the UPDATE statement in your code with the following and let us know what happens.

UPDATE addr

SET lpn = @newLPN

FROM dbo.Addresses addr

WHERE AccountID = @currentID

;

Wait a minute, Jeff!

You suggest to "simply" add a FROM clause to the update statement? (The alias isn't mandatory, is it?)

Interesting, what kind of "previous experience" you can refer to... (I'm not sure if I'm supposed to feel glad not to have faced such a situation. Yet!?! ;-))

Oddly enough, yes... that's pretty much what I'm suggesting. And update the alias like I did instead of the table. It was an undocumented trick in older versions of SQL Server long before the documentation in Books Online caught up with it and it was also the easiest way to do a self-joined update using two aliases for the same table in the FROM clause.

The reason I'm suggesting it is because we discovered, quite by accident, that it can make substantial improvements in performance by giving the optimizer better direction through code. Normally this problem has only appeared on improperly formed JOINed updates where the target table wasn't included in the FROM clause but was included in the WHERE clause. And when I say "appeared", I mean appeared in Spades. My first experience (more years ago) with the problem caused a normally 20 second update to pin 4 CPUs to the proverbial wall for two hours!

A long time ago, certain folks took exception to me blaming this type of problem on "Halloweening" because MS supposedly has code behind the UPDATE statement to prevent it, but it sure does have all the ear marks of "Halloweening" especially since this particular problem in generating thousands of unwanted rows.

Lutz - I am going to rewrite it set-based, sure.

As I see it, cursors are really meant for interaction between SQL Server

and applications which just cannot understand set-based operations.

Not within a stored procedure.

As I said, I didn't write it, I am having to dig into it as it suddenly blocked

the application.

To your other points - the sproc is not being called multiple times, I have

verified this.

I did execute it directly from Mgmt Studio and it did the same - it never returns for

minutes, meanwhile adding hundreds of thousands of records to the lpn table.

I already created a new procedure with the same code, just to be sure

that the sproc object isn't corrupt in some way. Didn't change a thing.

Good idea to put a WHERE condition.

There SHOULD not be a point trying to filter the records - based on what

the app is doing with it. Yes, if you run this twice, it will update these

records again. (The 'HR' is my rewrite, it is a parameter of the sproc

really). This is "allowable" in the application because t it won't ever

(presumably) call the sproc with the same parameter twice.

Jeff - yes I can put the FROM Addresses in the query, but it doesn't

change the behaviour. It also should not.

I'll solve the problem by rewriting. It still bugs me why on earth this

seemingly innocent code just overnight decided to mess up?

Are you SURE nothing changed in the code?

The likely culprit looks to be here:

DECLARE cursorlpn CURSOR FOR

select AccountID from Addresses

where country = 'HR';

I'm making a giant assumption, but how many addresses have the same accountID? Not sure what kind of account this might be- but the accounts where I work have many addresses. Assuming yours did too, then the cursor would "loop" for each duplicated accountID in your addresses table.

To check for duplicates:

select accountID, count(*) from addresses where

country = 'HR'

group by accountid

having count(*)>1

Edit: fixed the missing GROUP BY clause.

Thanks for the feedback, Peter. It was a shot in the dark from successes for slightly similar problems where such things go crazy on their own.

Is there any way you can provide a setup that could be used to reproduce the odd behavior of the sproc on a standalone machine (meaning that we can test against)?

There has to be a reason for the repetitive call.

And I agree: it's always good to know WHY it occured instead of just making it disappear by "just" rewriting the code...