Microsoft KB Archive/184497

= BUG: Fetching from a Cursor with Existence Clause May Cause an Access Violation =

Article ID: 184497

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Service Pack 3

-



This article was previously published under Q184497



BUG #: 17591 (SQLBUG_65)



SYMPTOMS
Fetching from a cursor may generate a handled access violation (AV) and the client may never receive any results or messages.



CAUSE
This problem can be caused when both of the following conditions are true:


 * All tables involved in the FROM clause do not contain a unique index.

-and-
 * The keywords EXISTS or NOT EXISTS are used in the WHERE clause.



WORKAROUND
To work around this problem, do any one of the following:


 * Ensure that all tables involved in the FROM clause contain a unique index.

-or-
 * Change the cursor from a DYNAMIC to a SCROLL or INSENSITIVE cursor.

-or-
 * Turn on trace flag 7501 to force the cursor to be resolved as a keyset cursor.



STATUS
Microsoft has confirmed this to be a problem in SQL Server version 6.5 Service Pack 3 and later. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.



MORE INFORMATION
The following script is an example:

USE pubs GO

PRINT 'The following processes will test WITHOUT an index on  titleauthors' PRINT 'Dropping constrainst on titleauthor table' alter table titleauthor drop constraint UPKCL_taind alter table titleauthor drop constraint FK__titleauth__title__14070484 PRINT 'Dropping index on titleauthor table' drop index titleauthor.auidind drop index titleauthor.titleidind go

-- Executing the query with a WHERE EXISTS on dynamic forward only -- cursor PRINT 'Executing the base query a dynamic forward only cursor' DECLARE avtest CURSOR FOR SELECT  au_lname , au_fname FROM   authors a         , titleauthor ta   WHERE a.au_id = ta.au_id AND EXISTS -- OR use NOT EXISTS (           SELECT   *            FROM   publishers p            WHERE    a.city = p.city         ) DECLARE @count int SELECT @count = 0

OPEN avtest FETCH NEXT FROM avtest WHILE (@@FETCH_STATUS <> -1) BEGIN FETCH NEXT FROM avtest select @count = @count + 1 END CLOSE avtest DEALLOCATE avtest select @count

PRINT 'Please run the instpubs.sql script to reinstall the pubs database'

Additional query words: SP SP3

Keywords: kbbug kbpending KB184497

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.