Microsoft KB Archive/282970

= BUG: FAST_FORWARD Cursor that Joins Tables with Triggers are not Always Converted to Static Cursors =

Article ID: 282970

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q282970



BUG #: 351564 (SHILOH)



SYMPTOMS
In the Microsoft SQL Server Books Online topic Forward-only Cursors (expand the Accessing and Changing Relational Data folder, expand the Cursors folder, expand the Cursor Types folder), expand the Forward-only Cursors folder, and then click the Fast Forward-only Cursors topic. Scroll down to the Implicit Conversion of Fast Forward-only Cursors section. The first bullet item under the Implicit Conversion of Fast Forward-only Cursors section states:

If the SELECT statement joins one or more tables with triggers to tables without triggers, the cursor is converted to a static cursor.

The script in the &quot;More Information&quot; section shows that this statement is not always correct.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 2000.



MORE INFORMATION
The following script demonstrates a cursor that is declared as a FAST_FORWARD cursor. The FAST_FORWARD cursor joins a table with a trigger to a table that does not have a trigger. The cursor is not converted to a STATIC cursor. USE NORTHWIND GO

CREATE TRIGGER isr_Categories ON Categories AFTER INSERT, UPDATE, DELETE AS SET NOCOUNT ON DECLARE @c int SET @c = 1 GO

-- Create a FAST_FORWARD cursor. -- Joining a table with triggers (Categories) -- to a table without triggers (Products)

BEGIN TRAN

DECLARE ArtCat CURSOR FAST_FORWARD FOR SELECT C.CategoryID, CategoryName, ProductName FROM Categories C JOIN Products P ON P.CategoryID = C.categoryID ORDER By C.categoryID

-- The cursor should have been converted to a STATIC cursor.

OPEN ArtCat FETCH NEXT FROM ArtCat

-- Update the Categories table. -- This change affects the next row in the cursor. -- If the cursor was STATIC you could not see -- this change through the cursor.

UPDATE Categories SET CategoryName = 'Condimentssss' WHERE CategoryID = 1

-- If the cursor was STATIC, you would see -- the old value &quot;Condiments&quot;. -- However, you see the new value &quot;Condimentssss&quot;

FETCH NEXT FROM ArtCat DECLARE @c cursor EXEC master.dbo.sp_describe_cursor @cursor_return = @c OUTPUT, @cursor_source = N'global', @cursor_identity = N'ArtCat'

-- If the cursor was STATIC, you would see -- Model = 1 (STATIC) in the following output. -- However, you see Model = 4 (FAST_FORWARD).

FETCH NEXT from @c CLOSE @c DEALLOCATE @c CLOSE ArtCat DEALLOCATE ArtCat ROLLBACK TRAN GO

Keywords: kbbug kbpending KB282970

-

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

© Microsoft Corporation. All rights reserved.