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

• Microsoft SQL Server version 6.5 Service Pack 3 and later

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          : SSrvGPF SSrvTran_SQL kbbug6.50.sp3 kbbug6.50.sp4 
Version           : WINNT:6.5 SP3
Platform          : winnt 
Issue type        : kbbug 

Last Reviewed: April 20, 1999