BUG: AV on Cursor with Arithmetic Operation and ORDER BY Clause (177007)

SYMPTOMS

SQL Server generates an access violation (AV) when declaring a dynamic cursor that contains an arithmetic operation on a column that is contained in an ORDER BY clause. The AV does not occur if the arithmetic operation is performed on the first column in the ORDER BY clause. However, performing the arithmetic operation on any other column position in the ORDER BY clause does cause the AV.

WORKAROUND

To work around this problem, do either of the following:

Switch the cursor type to keyset (SCROLL) or static (INSENSITIVE).

-or-
Change the cursor so the column on which the arithmetic operation is performed is the first column in the ORDER BY list.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5. 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 will reproduce the access violation:

CREATE TABLE dbo.ftt (
ftt_date datetime NULL ,
ftt_amount smallmoney NULL ,
ftt_vat smallmoney NULL ,
ftt_int int
)
GO

declare test cursor
for
select (ftt_amount+ftt_vat)
from ftt
order by ftt_date, (ftt_amount+ftt_vat), ftt_int

open test
close test
deallocate test
go

drop table ftt
go

If the ORDER BY clause is changed so that the arithmetic operation is the first column in the ORDER BY, the access violation does not occur. The following is an example of this change for the problem scenario described above:

order by (ftt_amount+ftt_vat), ftt_date, ftt_int