Hello!
I inherited a SS2K application (no chance of upgrade) with a field in a table typed as NVARCHAR(4000) ... storing non-Latin character text.
No I have been tasked with converting the field over to NTEXT so that the users can store more text. I understand my choices for writing the text are UPDATETEXT and WRITETEXT. I need to change a stored procedure so that one NTEXT field in one row has its contents completely replaced. BOL is not clear to me.
Q1: Do I want to use UPDATETEXT or WRITETEXT?
Q2: How do I determine the values of the
insert_offset and
delete_length when I want to
completely replace the contents with new text in one field in one row?
I think I want to use zero for the insert_offset but I am not sure about the delete_length. I have tried 1) a non-zero value, 2) null value, and a 3) zero value. My data is not being replaced.
Here is my code:
Code:
DECLARE @ptrval binary(16)
BEGIN TRAN
SELECT @ptrval = TEXTPTR(message) FROM LC_Forum_Messages
WHERE MessageID = @MessageID
UPDATETEXT LC_Forum_Messages.Message @ptrval 0 0 @NewMessage
-- @NewMessage is an input parameter to the stored procedure.
-- <some error handling left out>
COMMIT TRAN
Besides using "0", I have used
- Null and
- DATALENGTH(@NewMessage) / 2 -- I understand datalength is in bytes and I need characters. There are 2 bytes per NTEXT character.
For reasons I don't understand, I am not able to replace the old data with the new.
Any ideas of what I am missing?
Any recommendations (short of switching to SS2005) of how to do this better?
Thanks ahead of time for your help!
Replacing Text in NTEXT field
