[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 502
  • Last Modified:

cannot update a text, ntext, or image column and a clustering key

Hi There

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:

Table1
--------
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?

Thanx
0
michaelpg
Asked:
michaelpg
  • 7
  • 5
  • 2
1 Solution
 
Anthony PerkinsCommented:
>>Can anyone out there please shed some light on this for me?<<
Is changing your text column to a varchar a viable alternative?
0
 
michaelpgAuthor Commented:
Hi Guys

Firstly thanx acperkins changing it to a char is not viable unfortunately because because of time constraints.

rafrancisco, thanx for the assistance.
Ok where do i start.
There are no primary keys involved.The table does not have a primary key only a clustered index(perhaps for this situation one could view it as the same thing).

Also the examples provided by the link does not clarify why the first update would result in an error but not the second? I have played around with the sql provided by the link and the second type of update never gives an error only the update using the replication function? My update does not use the replicate function it is like the second update that is commented as will not give the error yet it does?

That is primarily the problem using the sql i provided originally this error cannot be invoked.So why does the exact same sql applied on the identical table structure on production give this error?

Unfortunately dropping the clustered index is not an option.

Try this Example:

create table #t1 (c1 int not null, c2 int not null, c3 text, c4 int
identity)
go
alter table #t1 add primary key (c1,c2)
go
insert #t1 values (1,7,'test')
go
update #t1 set c2=10, c3='test again' where c2=7 ---This won't show the
error - ok fine why not a clustered index and text field are being updated?
go
update #t1 set c2=7, c3='test again' where c2=10 ---This won't show the
error - ok fine why not a clustered index and text field are being updated again?
go
update #t1 set c2=10, c3=replicate('x',8000)+replica­te('y',8000) where c2=7
-- This will show the error - ok why? Repicate should only be updating the text field with a very long string of x's and y's as opposed to "test again", so what are they trying to say this error occurs only when the text column update is a huge string.I still dont get it.

What is the difference between the update that works and the update that fails that invokes the error besides the size of the text field update?

Once again my update is like the first 2 provided above that never produces the error.

Sorry if i am wasting your time, but i still dont get it.

Thanx
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
rafranciscoCommented:
One reason I can think of why your first 2 updates work and the second 1 didn't is just like what you said, you updated the text field with a long string.  When you updated it, the contents of the new record will not fit in a single data page.  So SQL has to split it up, which it can't do as a limitation.  The strings you were trying to update with the first 2 updates were short enough that the whole row fits in the same data page.

Hope this helps in anyway.
0
 
michaelpgAuthor Commented:
Hi

Ok that does make some sense but i am updating it with a short string an email address actually and on production i keep getting the error?
0
 
michaelpgAuthor Commented:
Ok i figured it out, thanx for the assistance rafrancisco you can have the points.
0
 
Anthony PerkinsCommented:
>>Ok i figured it out, thanx for the assistance rafrancisco you can have the points.<<
I realize you are new here, so I suggest you read the EE Guidelines regarding grading standards at:
What's the right grade to give?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi73

And specifically this section:

<quote>
C: Because Experts' reliability are often judged by their grading records, many Experts would like the opportunity to clarify if you have questions about their solutions. If you have given the Expert(s) ample time to respond to your clarification posts and you have responded to each of their posts providing requested information; or if the answers, after clarification, lack finality or do not completely address the issue presented, then a "C" grade is an option. You also have the option here of just asking Community Support to delete the question.

Remember, the Expert helping you today is probably going to be helping you next time you post a question. Give them a fair chance to earn an 'Excellent!' grade and they'll provide you with some amazing support. It's also true that a "C" is the lowest grade you can give, and the Experts know that -- so use it judiciously.

Only the Moderators and Page Editors have the choice to give a D grade. Beyond that, in a practical sense, the grading guidelines have "softened" a bit over the last year or two; one might expect that the majority of grades would be Bs (a standard "bell" curve), but the fact is that the culture of the site has caused there to be an inordinately high percentage of As. The Moderators have been instructed to ensure that the As they award are actually "Excellent" answers. Similarly, the C grade is the lowest that can be given by a member, a fact which should be kept in mind when grading as well.

The use of a C in a vindictive manner is likely to be changed by a Moderator. You may not like the answer you get, and in some cases, and you may not like the way it is delivered, but if it is deemed to be accurate, no less than a B is an acceptable grade.
</quote>

Thanks.
0
 
michaelpgAuthor Commented:
Hi

I apologise if you feel offended by the C grade.
I simply have no idea how to cancel a question if it has been open for a long time without getting an answer, i felt i would give you the points because of the effort put in to assist me, but i never got any clarity or the answer the exactly what the problem was, so i gave a C.

0
 
Anthony PerkinsCommented:
>>I apologise if you feel offended by the C grade.<<
There seems to be some confusion here: You did not "award" me the points, you awarded rafrancisco.

>>I simply have no idea how to cancel a question if it has been open for a long time without getting an answer<<
It is all covered in the EE Guidelines.  See here:
What are my choices?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi67

>>but i never got any clarity or the answer the exactly what the problem was, so i gave a C.<<
As you now know that is not the way it works here.
0
 
michaelpgAuthor Commented:
Ok i have read the link, i am still happy with what i did.

I awarded all the points to rafrancisco as he provided me with 99% assistance and feedback.
I awarded a C grade as it lacked finality, also reccomended on the link.

I have a feeling becasue i figured it out myself i was supposed to take the route to ask for a refund and ask a moderator to close the question.

I did not find that appropriate, first if the answer was purely sql i would post the answer i found and close the question.
The answer was application related and therefore had no further bearing on the question i posted, i felt that the effort rafrancisco had made to help me was considerable and he deserved the points but awarded a C grade as we never actually got to the root of the problem in terms of sql.

Hope this is ok with you.

Cheers
0
 
Anthony PerkinsCommented:
>>I have a feeling becasue i figured it out myself i was supposed to take the route to ask for a refund and ask a moderator to close the question.<<
There are other avenues you can take.  You can post a message in Community Support explaining the situation and they can reduce the points, if you do not feel the solution covered the question.

But the point is that you should only use a C grade as a last resort.  You should understand that many experts will see that grade in your history and will think twice about helping you.

But your are right it is your choice, just understand the consequences.
0
 
michaelpgAuthor Commented:
Ok fair enough, i had no idea a C was such a serious issue, i guess in hindsight a B would have been the correct one.

I cannot believe a C in your history would stop people from helping you?
Why exactly is that? I mean i normally give A's all the time because my question was comprehensively answered, but this question still left me feeling very confused and not grasping the real problem.

I also feel it is not fair to refund the question because people have put in the effort to help you and even though the question is not resolved i believe they deserve the points, but then obviously i am not going to give an A.

How will it negatively effect rafrancisco? If it does i do apologize , guess there is no way i can take it back?
0
 
Anthony PerkinsCommented:
>>I also feel it is not fair to refund the question because people have put in the effort to help you and even though the question is not resolved i believe they deserve the points, but then obviously i am not going to give an A.<<
Right, there is another solution, as I mentioned in my previous comment you can request the points be reduced.  In this case, for example, if you had requested the points reduced to 250 and awarded an "A", the total number of points actually awarded to the experts would have been the same, without the stigma of a "C".

>>How will it negatively effect rafrancisco?<<
It does not, it affects you.  This is not your case, but many experts will think twice about helping someone with a bad track record (be it grading or deleted questions).  In your case there is only one "C" grade, so that does not apply.

>>guess there is no way i can take it back?<<
See here:
Can I get a grade changed?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi18

Even if you do not take this any further, I would like to thank you for your concern.  Trust me not everyone is as responsive or even care to respond.
0
 
michaelpgAuthor Commented:
Thanx for the help acperkins.

Like you said i am relatively new to experts exchange, at least now i know proper procedures and the options i have, much appreciated.
I will look into changing the grade.

Thanx for the time and patience.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now