Problem with UPDATETEXT

Question

Problem with UPDATETEXT

Subair

SSC Journeyman

Points: 76
More actions
November 2, 2006 at 3:34 am

#117353

Hi,

I'm getting the error "Offset 282 is not in the range of available text, ntext, or image data.
The statement has been terminated." while running the below query.
Could any one tell me what is the problem?
SET nocount ON

DECLARE  @TextPointer varbinary( 16 ) -- Stores the handle FOR the TEXT field to be processed
DECLARE  @DeleteLength int  -- Stores the length OF string to be removed
DECLARE  @OffSet  int  -- Stores the starting position FOR the UPDATE

SELECT * INTO #Temp_Table from PS_PROJECT_DESCR
-- Step 01 : Obtain the handle for the TEXT field to be processed
SELECT  @TextPointer = textptr( DESCRLONG )
FROM  #Temp_Table

-- Step 02 : Set the delete length of string to be removed from the TEXT field
SET  @DeleteLength = len( ',' )

-- Step 03 : Initialise the starting position for the update
SET  @OffSet = 0

-- Step 04 : Loop thru table until all instances of the search string are replaced
WHILE  ( SELECT count( * )
   FROM #Temp_Table
   WHERE patindex( '%,%', DESCRLONG ) <> 0
&nbsp > 0
     BEGIN
       -- Step 04a : SET the starting position FOR the UPDATE
       SELECT  @OffSet = patindex( '%,%', DESCRLONG ) - 1
       FROM  #Temp_Table
       WHERE  patindex( '%,%', DESCRLONG ) <> 0

       -- Step 04b : Replace the search string WITH the replacement string
       UPDATETEXT #Temp_Table.DESCRLONG -- table_name.dest_column_name
         @TextPointer   -- dest_text_ptr
         @OffSet    -- insert_offset
         @DeleteLength   -- delete_length
         '~'  -- inserted_data
end
SET nocount OFF
select * from #Temp_Tbl
drop table #Temp_Table
Regards,
Ashar

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Ian Yates SSCoach Points: 19738 More actions · Answer 1

Please don't cross post - or if you must then please include references between your threads so that other members don't answer your question when it's alreay been answered elsewhere. Thread continues: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=23&messageid=319839

Cheers

Subair SSC Journeyman Points: 76 More actions · Answer 2

oops.. I'm sorry.. This is a bit urgent thats y i posted in different sections..