cannot update a text, ntext, or image column and a clustering key
Posted on 2005-04-25
I get the following error when trying to do an update.
***The query processor could 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***
This is pretty self explanatory, except that when i try to do the same statement on our dev database it is successfull(the table and index structures are identical).
We have a table roughly something like this:
Column1 - char - col1 of clustered index
Column2 - char - col2 of clustered index
Column3 - char - col3 of clustered index
Column4 - datetime - col4 of clustered index
Column5 - int
Column6 - text
The statement goes something like this.
UPDATE Table1 set Column1 = '00021', Column2 = '1234', Column3 = 'check', Column4 = '2005-04-23 12:33:47.000',
Column6 = 'new long email address'
WHERE Column1 = '00021' AND Column2='1234' AND Column3='check' AND Column4='2005-04-24 12:33:47.000'
AND Column6 = 'old long email address'
now this statement which is identical to the one on production runs successfully on the dev database? I am clearly updating the clustered index with a new value and the text field so why do i not get an error about updating a clustered index and text field?
I have checked Books online and they say this error only occurs when updating multiple rows.
BOL***If an update query could alter more than one row while updating both the clustering key and one or more text, image, or Unicode columns, the update operation fails and SQL Server returns an error message***
So i tried the above sql except with a much less specific where clause so that multiple rows would be updated and still no error it went through successfully?
Can anyone out there please shed some light on this for me?