FIX: Forward Only and Dynamic Cursors May Infinitely Loop

ID: Q220154

The information in this article applies to:

Microsoft SQL Server version 6.5 Service Pack 5 and 5a

BUG #: 18614 (SQLBUG_65)

SYMPTOMS

This bug is seen only in Service Pack 5a (not in SP5 or earlier). When Forward Only or Dynamic cursors are used, fetch operations may result in an infinite loop, and return an infinite result set. This only happens on a table that has a non-unique index. This can happen on a clustered or a non-clustered index.

CAUSE

This is a regression caused by a bug fix between Service Pack 5 and Service Pack 5a.

RESOLUTION

A supported fix that corrects this problem is now available from Microsoft, but it has not been fully regression tested and should be applied only to systems experiencing this specific problem. If you are not severely affected by this specific problem, Microsoft recommends that you wait for the next SQL Server service pack that contains this fix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information on support costs, please go to the following address on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

The English version of this fix should have the following file attributes or later:


   Date        Time   Version   Size       File name    Platform
   -------------------------------------------------------------
   04/23/1999  14:38  6.50.434  2,881,204  sql434i.exe  (x86)
   04/23/1999  14:45  6.50.434  4,735,899  sql434a.exe  (alpha)

NOTE: Due to file dependencies, the most recent hotfix or feature that contains the above files may also contain additional files.

NOTE: If this product was already installed on your computer when you purchased it from the Original Equipment Manufacturer (OEM) and you need this fix, please call the Pay Per Incident number listed on the above Web site. If you contact Microsoft to obtain this fix, and if it is determined that you only require the fix you requested, no fee will be charged. However, if you request additional technical support, and if your no-charge technical support period has expired, or if you are not eligible for standard no-charge technical support, you may be charged a non-refundable fee.

For more information about eligibility for no-charge technical support, see the following article in the Microsoft Knowledge Base:

Q154871 Determining If You Are Eligible for No-Charge Technical Support

WORKAROUND

There are two workarounds for this bug. Different scenarios will determine which one is best for you.

If possible, use a unique index instead of the non-unique.

Use keyset or static cursors instead of the dynamic or forward-only types.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5.

MORE INFORMATION

Cursors used in SQL Server can be implemented in several different ways via T-SQL statements, ODBC applications, and so on. The behavior in this article can be seen in many forms under many different scenarios, but all are based on the same problem.

Basically, if forward only or dynamic cursors are used on a table with a non-unique index, you may run into the problem described.

This problem can be seen from many different symptoms not limited to, but including some of the following: applications are timing out, the server seems to hang on a query with the use of cursors, or many more rows are being returned than expected.

The cursor type may be changed to prevent this problem, but this may require major work depending on where the cursors are used. For instance, if many applications had been rolled out which make calls to implement cursors, a major re-rollout may be necessary. If cursors are done on the server side, then this may be more practical.

The most solid workaround is to change the index on the table to be unique. This could mean using a clustered or non-clustered index that does not allow duplicate key values. This may also be the least practical solution because changing the schema could result in a major rework of the database and the applications that interact with it.

Additional query words: cursor index hang SP5a SP5 regression

Keywords :
Version : winnt:6.5 Service Pack 5 and 5a
Platform : winnt
Issue type : kbbug