BUG: DECLARE CURSOR on View with DISTINCT Causes AV

• Microsoft SQL Server, version 6.0

SYMPTOMS

When you declare a cursor on a view with DISTINCT, the operation will cause a handled access violation (AV) resulting in the client process being terminated. The same behavior can occur in both Transact-SQL and in applications written to use server cursors. The behavior only occurs if the table has a unique index.

WORKAROUND

Rewrite the query so that you are using DISTINCT in the cursor declaration rather than in the view declaration. Another possibility is to drop the unique index on the table if it is not being used for integrity constraints. DB-Library and ODBC applications can also use their respective cursor libraries.

STATUS

Microsoft has confirmed this to be a problem in SQL Server NT version 6.0. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.