I am getting an @@SQLSTATUS = 2 when there are more records to fetch. Adding
an order by to the select statement that creates the cursor seems to solve
this. However, I would rather not have the order by there for performance
reasons. Does anyone know what a solution to the problem is? Is there a
server configuration that may be set wrong? An example is included below.
Any help is greatly appreciated.
Solutions Plus, Inc.
DECLARE cNotices CURSOR FOR
SELECT Number FROM Notices
WHERE PurgeDate <= @PurgeDate order by number
FETCH cNotices INTO @NUMBER
WHILE @@SQLSTATUS = 0
DELETE FROM Notices WHERE Number = @NUMBER
FETCH cNotices INTO @NUMBER
DEALLOCATE CURSOR cNotices
Subject: @@SQLSTATUS = 2 when there are more records
Date: Fri, 21 Jul 2000 11:24:46 -0500
X-Newsreader: Microsoft Outlook Express 5.00.2615.200
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200
Xref: forums-1-dub sybase.public.sqlserver.nt:1840
Article PK: 1089326
Date: Sat, 22 Jul 2000 07:57:35 +1200
From: "Mark A. Parsons" <pegasys@_internet.co.nz>
Organization: Pegasys (2000) Limited
X-Mailer: Mozilla 4.72 [en] (Win98; U)
Subject: Re: @@SQLSTATUS = 2 when there are more records
Content-Type: text/plain; charset=us-ascii
NNTP-Posting-Host: p502.ipa1-n8-16.iconz.net.nz 220.127.116.11
Xref: forums-1-dub sybase.public.sqlserver.nt:1836
Article PK: 1089319
I'm gonna guess that 'PurgeDate' does *NOT* have a unique date on it. If this
is the case ... your deletes, from the same table you've built the cursor upon,
are probably screwing up the cursor's positioning algorithm ... not quite the
same thing, but similar to the halloween problem.
If you had a unique index on 'PurgeDate' then the cursor should not have these
problems (you'll have to test this to be sure).
The reason why the 'order by' works is that the cursor has to build a worktable
to contain all of the rows (i.e., a *copy* of the 'real' table), so that the
cursor is really stepping through the worktable, instead of the 'real' table.
This means it can perform it's deletes on the 'real' table without affecting
it's positioning algorithm in the worktable.
One other option you might test ... and I'm not saying this would actually work
... is to remove the 'order by' from the cursor, and use the 'where current of'
clause with your delete.
Cursors start to get real finicky when you start moving their base rows around
On another note ... is this just an example of something similar to what you're
doing? or is this *actually* what you're attempting to do?
Just wondering why you'd use a cursor for this particular operation when it
would be quicker, and more efficient, to just delete directly from the base
table based on your 'PurgeDate' condition (no use of cursor):
delete Notices where PurgeDate <= @PurgeDate
Mark A. Parsons
Iron Horse, Inc. firstname.lastname@example.org
Pegasys (200), Ltd email@example.com