PRB: SQL Server Cursor Type May Change From Dynamic to Keyset

• Microsoft SQL Server, version 6.5

SYMPTOMS

Under certain circumstances, on a table with few rows, a keyset cursor is created on a SELECT statement with an ORDER BY clause even when a dynamic or a server-side forward_only cursor is specified and all the rules for creating dynamic/forward_only cursors are satisfied.

WORKAROUND

Specify an explicit optimizer hint to use the unique index that you created on the column(s) used in the ORDER BY clause of the SELECT statement.

In ISQL/W run the following script:

create table test(vendor int not null , upc char(12) not null) go create unique index ind1 on test(upc) go create unique index ind2 on test(vendor) go insert test values(20,'ABC Vision') go insert test values(10,'XYZ Tech')

In ODBC Test:

1. FullConnect to the datasource that points to the database that has the TEST table created above.

2. Call SQLSetScrollOptions and specify SQL_CONCUR_VALUES, SQL_SCROLL_DYNAMIC, and 1.

   You can also use SQL_CONCUR_READ_ONLY, SQL_SCROLL_FORWARD_ONLY, and 2.

3. SQLExecDirect on "select * from test where vendor=20 and upc>='ABC Vision' order by vendor"

   This will give a SQL_SUCCESS_WITH_INFO and the cursor is changed to keyset type.

   Select * from test (index=ind2)  where vendor=20 and upc>='ABC Vision'
   order by vendor