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

• Microsoft SQL Server versions 6.5

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

   order by (ftt_amount+ftt_vat), ftt_date, ftt_int