FIX: Updating a Text Column Results in a Table Scan

 Last reviewed: May 1, 1997
Article ID: Q135714

The information in this article applies to:
  • Microsoft Open Data Base 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 <table-name> SET <text-column name>='#odbc#xxxxx' WHERE
   <search condition>
   SELECT TEXTPTR(<text-column name>) from <table-name> WHERE
   <text-column name> LIKE '#odbc#xxxxx'
   WRITETEXT BULK<table-name>.<text-column name> 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 : kbbug2.50.0121 kbbug6.00 kbprg SSrvProg SSrvTran_SQL
Version : 2.5.0121 6.0
Platform : WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 1, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.