Microsoft KB Archive/135714

{|
 * width="100%"|

FIX: Updating a Text Column Results in a Table Scan

 * }

Q135714

-

The information in this article applies to:


 * Microsoft Open Database Connectivity, version 2.5
 * Microsoft SQL Server version 6.0

-

BUG# NT: 10959 (6.00)

SYMPTOMS
When you use the SQL Server ODBC driver version 2.50.0121 to update a text column, a table scan is performed and the update may take a very long time depending on the number of rows.

CAUSE
Applications that use ODBC typically call the SQLPutData API to update text and image columns. With the new SQL Server driver, SQLPutData internally issues the following statements to the SQL Server:

  BEGIN TRAN SET TEXTSIZE 2147483646 UPDATE  SET ='#odbc#xxxxx' WHERE SELECT TEXTPTR() from  WHERE  LIKE '#odbc#xxxxx' WRITETEXT BULK. 0xxxxxxx WITH LOG COMMIT TRAN

The SELECT statement uses a LIKE on the text column to get the text pointer. This causes a table scan, and if the table has a significant number of rows, takes a considerable amount of time. The overall effect is a slower UPDATE.

STATUS
Microsoft has confirmed this to be a problem in the 2.50.0121 version of the SQL Server ODBC driver. This problem was corrected in Service Pack 1 for SQL Server version 6.0. For more information, contact your primary support provider.

Additional query words: sql6 mfc

Keywords : kbprogramming

Issue type : kbbug

Technology : kbSQLServSearch kbAudDeveloper kbSQLServ600 kbODBCSearch kbODBC250