DECLARE c CURSOR GLOBAL FORWARD_ONLY DYNAMIC SCROLL_LOCKS TYPE_WARNING FOR
SELECT
TxtPtr = TEXTPTR(BC.BODY),
Src = N'replacedText',
Offset = PATINDEX(N'%originalText%', BC.BODY) - 1
length = CAST(LEN('originalText') as varchar(5))    ----Here is the problem!
FROM
dbo.BODYCONTENT AS BC
WHERE
BC.BODY LIKE N'%originalText%';
OPEN c;
DECLARE @Ptr binary(16), @Src nvarchar(50), @Offset integer;
SET STATISTICS XML OFF; -- No cursor fetch plans
BEGIN TRANSACTION;
WHILE 1 = 1
BEGIN
FETCH c INTO @Ptr, @Src, @Offset;
IF @@FETCH_STATUS = -2 CONTINUE; -- row missing
IF @@FETCH_STATUS = -1 BREAK; -- no more rows
IF 1 = TEXTVALID('dbo.BODYCONTENT.BODY', @Ptr)
BEGIN
-- Modify ntext value
UPDATETEXT dbo.BODYCONTENT.BODY @Ptr @Offset @length @Src;
END;
END;
COMMIT TRANSACTION;
CLOSE c; DEALLOCATE c;