SQL SERVER – Fix : Error 8629 The query processor could not produce a query plan from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time.

Error:

Error : 8629 The query processor could not produce a query plan from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time.

Fix/WorkAround/Solution:
This error happens usually when multiple rows are updated and their clustering key is part of update. If multiple row of which clustering keys are updated there is always possibility that they end up having same value, which will violate the clustering key requirements.

Update one row at the time in this situation. Use while loop or cursor (I do not recommend cursor usage)

Reference : Pinal Dave (http://blog.SQLAuthority.com)

3 thoughts on “SQL SERVER – Fix : Error 8629 The query processor could not produce a query plan from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time.

  1. Hello Pinal,

    My project was running successfully, all-of-sudden I get same error. In my case there is no cursor, but I am using PIVOT, from googling I found that its bug and there is patch for this.

    Is this really bug?

    Like

  2. Hello Imran,

    When there are patches available it means bug existed in existing product.

    It is quite common that bug suddenly shows up in the system which is running perfectly fine earlier. There are some bugs which shows up when certain conditions are met and patches are necessary at that time.

    Kind Regards,
    Pinal

    Like

  3. Hi pinaldave,
    I am having then same problem but in a different query.
    The Query is:
    SELECT
    c.colors,
    YomanBS.*
    FROM YomanBS
    outer APPLY
    (
    SELECT YP.colorid + ‘,’ AS [text()] FROM yomanbsparitim AS YP
    INNER JOIN yomanbsnigrar AS YN ON YN.kodparit = YP.kodparit
    WHERE yn.misparhazmana = YomanBS.recordid
    FOR XML PATH(”)
    ) AS c (colors)
    WHERE ISNULL(status,0) = 0 AND LeTarik IS NOT NULL AND letarik BETWEEN ‘2014-12-01′ AND ‘2014-12-01′
    ORDER BY Letarik

    The Error I get:
    Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

    Instance Info:
    Edition:Express Edition with Advanced Services (64-bit)
    ProductVersion: 10.50.1600.1
    ProductLevel: RTM

    I have already tried:
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    Before the query.

    Bye the way the problem is only when I am running the query from my App,
    when I am running it from SSMS the query runs with no errors.
    Thanks In advance.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s