Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

@@SQLSTATUS = 2 when there are more records

2 posts in Windows NT Last posting was on 2000-07-21 19:57:35.0Z
Scott Landers Posted on 2000-07-21 16:24:46.0Z
From: "Scott Landers" <scott770@bellsouth.net>
Subject: @@SQLSTATUS = 2 when there are more records
Date: Fri, 21 Jul 2000 11:24:46 -0500
Lines: 31
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2615.200
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200
Message-ID: <epEB3Nz8$GA.203@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: 38.216.214.75
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1840
Article PK: 1089326

All,

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.

Scott Landers
Solutions Plus, Inc.

DECLARE cNotices CURSOR FOR
SELECT Number FROM Notices
WHERE PurgeDate <= @PurgeDate order by number

OPEN cNotices

FETCH cNotices INTO @NUMBER
WHILE @@SQLSTATUS = 0
BEGIN
BEGIN TRANSACTION
DELETE FROM Notices WHERE Number = @NUMBER
COMMIT
FETCH cNotices INTO @NUMBER
END

CLOSE cNotices
DEALLOCATE CURSOR cNotices


"Mark A. Parsons" <pegasys Posted on 2000-07-21 19:57:35.0Z
Message-ID: <3978AB2F.E734F57C@_internet.co.nz>
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)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: @@SQLSTATUS = 2 when there are more records
References: <epEB3Nz8$GA.203@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 50
NNTP-Posting-Host: p502.ipa1-n8-16.iconz.net.nz 210.48.25.246
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1836
Article PK: 1089319


> DECLARE cNotices CURSOR FOR
> SELECT Number FROM Notices
> WHERE PurgeDate <= @PurgeDate order by number
>
> OPEN cNotices
>
> FETCH cNotices INTO @NUMBER
> WHILE @@SQLSTATUS = 0
> BEGIN
> BEGIN TRANSACTION
> DELETE FROM Notices WHERE Number = @NUMBER
> COMMIT
> FETCH cNotices INTO @NUMBER
> END

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 them.

--------------------

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. iron_horse@compuserve.com
Pegasys (200), Ltd pegasys@internet.co.nz