Q135714 - FIX: Updating a Text Column Results in a Table Scan

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 <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 : kbprogramming
Issue type : kbbug
Technology : kbSQLServSearch kbAudDeveloper kbSQLServ600 kbODBCSearch kbODBC250