FIX: Cursor Type and Column Updatability May Change with Some Sort Orders (310572)

SYMPTOMS

A cursor declared as DYNAMIC is actually open as KEYSET/OPTIMISTIC.

An ODBC application may receive the following error message when SQL Server updates a result set:

...runtime error 40038, can't assign value to non-updatable field

A call to the SQLColAttribute SQL_DESC_UPDATABLE property made on the column present in the ORDER BY clause returns SQL_ATTR_READWRITE_UNKNOWN (#define SQL_ATTR_READWRITE_UNKNOWN 2). Any subsequent update attempt on the result set column returns the error message mentioned earlier.

The error message occurs if all of the following conditions are true:

SQL Server is installed with a case and accent insensitive sort order (for instance, sort order 44 and sort order 54) or with a Cyrillic case-insensitive sort order (sort order 106).
A post Microsoft SQL Server 7.0 Service Pack 3 (SP3), Sqlservr.exe, hotfix with a product version between 7.00.979 and 7.00.1019 is installed.
The SELECT statement involved is of type "SELECT ORDER BY."
The column involved in the ORDER BY clause is a char or a varchar data type.

CAUSE

With a post SQL Server 7.0 SP3 hotfix installed, the execution plan steps of a SELECT statement of type SELECT ORDER BY are:

clustered index scan
   compute scalar (on the column present in ORDER BY)
        sort

Because of the COMPUTE SCALAR step, the column present in the ORDER BY clause is set as "not updatable."

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 7.0. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

301511 INF: How to Obtain the Latest SQL Server 7.0 Service Pack

NOTE: The following fix was created prior to Microsoft SQL Server 7.0 Service Pack 4:

Microsoft recommends that you apply this fix to your SQL Server 7.0 installation. SQL Server 7.0 Service Pack 3 is required to apply this fix.

NOTE: Microsoft SQL Server 7.0 (7.00.1020), or later, already contains the fix; therefore, you do not need to apply this hotfix if you are using SQL Server 7.0 (7.00.1020), or later.

Alpha

The following file is available for download from the Microsoft Download Center:

s71020a.exe

Release Date: JAN-24-2002

Intel

The following file is available for download from the Microsoft Download Center:

S71020i.exe

Release Date: JAN-24-2002

For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:

119591 How To Obtain Microsoft Support Files from Online Services

Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file.

To make sure that you have properly installed the fix, run the following command from SQL Query Analyzer or from the osql command prompt:

"SELECT @@VERSION" (without the quotation marks)

Depending on your platform, the result you receive is either:

"Microsoft SQL Server 7.00 - 7.00.1020 (Intel X86)" or greater.

-or-
"Microsoft SQL Server 7.0 - 7.00.1020 (Alpha)" or greater.