PRB: If You Update Partitioned Column on a Distributed Partitioned View Error 8626 May Occur When the Base Table has TEXT Column (270007)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q270007

SYMPTOMS

If you run an UPDATE statement against the partitioned column of a distributed partitioned view and the base table has a TEXT, NTEXT or IMAGE datatype column, the following error message occurs:
Server: Msg 8626, Level 16, State 1, Line 1
Only text pointers are allowed in work tables, never text, ntext, or
image columns. The query processor produced a query plan that required
a text, ntext, or image column in a work table.

CAUSE

Although the UPDATE statement you run updates only the partitioned column, the TEXT column is implicitly updated because rows may move from one partition to another. You cannot update the clustering columns and TEXT at the same time.

This behavior is by design.

WORKAROUND

To work around this behavior, use either of the following methods:
  • Perform a DELETE followed by an INSERT.

    -or-

  • Remove the TEXT, NTEXT or IMAGE datatype columns from the table.

REFERENCES

For more information about distributed partitioned views, please see the "Partitioning Data" topic in SQL Server Books Online.
Modification Type:MajorLast Reviewed:10/3/2003
Keywords:kbprb KB270007
©2003 Microsoft Corporation. All rights reserved.