Article ID: 220154
Article Last Modified on 9/6/2006
APPLIES TO
- Microsoft SQL Server 6.5 Service Pack 5a
This article was previously published under Q220154
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.
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. This problem has been corrected in the Post Service Pack 5a Update for Microsoft SQL Server version 6.5. To install the Post Service Pack 5a Update, you must have either SQL Server 6.5 SP5 or SP5a installed.
For information about how to download and install the SQL Server 6.5 Service Pack 5a, refer to the following article in the Microsoft Knowledge Base:
197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a
If you already have SQL Server 6.5 SP5 or SP5a installed, you can download the Post SP5a Update from the following article in the Microsoft Knowledge Base:
274036 INF: How to Obtain SQL Server 6.5 Post Service Pack 5a Update
For more information, contact your primary support provider.
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: kbbug kbfix kbqfe KB220154
