Updating a clustered index and a text column in a table at the same time
-
Dear all,
I'm using SQL Server 2000 SP3.
I'm having a problem with a stored procedure which updates a table that has a composite clustered key of 'id' (which is an identity column) and 'property_ref', which is a varchar(8). The table also has a column 'specify', which is a text. The stored procedure is trying to update both the 'property_ref' and 'specify' columns in the same T-SQL statement.
When I run the following procedure with the specified parameters, I get the following error -
EXEC usp_promis_save_property_condition_details '00001849',2,"XBOX 360",'04/10/2005','XXXXXXX',2653
"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."
Stored procedure code is as follows -
CREATE Procedure usp_promis_save_property_condition_details
(
@property_ref varchar(8),
@property_condition_id int,
@specify text,
@date_of_condition datetime,
@last_modified_by varchar(20),
@id int
)
As
declare @int_row_count int
begin transaction
-- see if there already is a row
set @int_row_count =
(
select count(*) from promis_property_condition_details
where id = @id
)
-- if there are no rows, then this is obviously a new entry
print @property_ref
if @int_row_count = 0
begin
insert into promis_property_condition_details
values
(
@property_ref,
@property_condition_id,
@specify,
@date_of_condition,
@last_modified_by,
getdate()
 
end
else
-- update the existing record
begin
update promis_property_condition_details
set
property_ref = @property_ref,
property_condition_id = @property_condition_id,
specify = @specify,
date_of_condition = @date_of_condition,
last_modified_by = @last_modified_by,
last_modified_date = getdate()
where id = @id
end
commit transaction
return
---------------------------------------------------------------------------------------------------------
However, when I run either of the above UPDATE or INSERT queries individually in query analyser, they work fine e.g.
---------------------------------------------------------------
insert into promis_property_condition_details
values
(
'00001849',2,"xbox400",'04/10/2005','XXXXXX', getdate()
 
--------------------------------------------------------------
or
--------------------------------------------------------------
update promis_property_condition_details
set
property_ref = '00001849',
property_condition_id = 2,
specify = "PSP SONY",
date_of_condition = '04/10/2005',
last_modified_by = 'XXXXXXX',
last_modified_date = getdate()
where id = 2653
--------------------------------------------------------------
I can also run the code thats in the SP in query analyser without any problems, as follows -
declare @property_ref varchar(8),
@property_condition_id int,
@specify varchar(8000),
@date_of_condition datetime,
@last_modified_by varchar(20),
@id int
declare @int_row_count int
set @property_ref = '00001849'
set @property_condition_id = 2
set @specify = 'extract'
set @date_of_condition = '04/10/2005'
set @last_modified_by = 'XXXXX'
--set @id = 0
set @id = 2653
begin transaction
-- see if there already is a row
set @int_row_count =
(
select count(*) from promis_property_condition_details
where id = @id
)
-- if there are no rows, then this is obviously a new entry
print @property_ref
if @int_row_count = 0
begin
insert into promis_property_condition_details
values
(
@property_ref,
@property_condition_id,
@specify,
@date_of_condition,
@last_modified_by,
getdate()
 
end
else
-- update the existing record
begin
update promis_property_condition_details
set
property_ref = @property_ref,
property_condition_id = @property_condition_id,
specify = @specify,
date_of_condition = @date_of_condition,
last_modified_by = @last_modified_by,
last_modified_date = getdate()
where id = @id
end
commit transaction
It appears to me that the optimizer is having problems when trying to update a clustered index and a text column at the same time in the context of a stored procedure.
Any thoughts,
Cheers,
Stephen
-
The different behaviour inside the SP may be due to parameter-sniffing and a resulting different execution plan for the SP, versus your standalone statements with hard-coded values instead of unknown parameters.
Does this article help ?
-
For updating text fields you must use UPDATETEXT.
There is full description and examples of use in BOL.
_____________
Code for TallyGenerator -
PW, thanks for the reply, I'll have a look at the article.
Sergiy, thanks for your reply. You say you have to use UPDATETEXT to update text fields. I was under the impression you could use standard INSERT/UPDATE statements to update text fields, as we are using in my examples. You can use UPDATETEXT to update specific portions of the data in a text field or when dealing with large amounts of data.
Am I incorrect with this assumption and the preferred method of updating text fields is by using UPDATETEXT, WRITETEXT etc.
Cheers,
Stephen
-
Actually in you script you can get rid of the problem by replacing UPDATE with DELETE and INSERT.
Because you are updating all fields you can delete row where id = @id first (if there is no such row nothing will be deleted) and than insert new row.
Your code will be twice shorter and you will not need to deal with text tricks.
Keep it simple!
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply