So I need to update a text field. Neither the UPDATE statement or the WRITETEXT statement work when used below
CREATE TABLE MyTable (IDField int, MyField text)
INSERT INTO MyTable (IDField) SELECT 1
DECLARE @Data1 varchar(8000), @Data2 varchar(8000), @ptrval binary(16)
SELECT @Data1 = REPLICATE('1',8000)
SELECT @Data2 = REPLICATE('2',8000)
-- this sets MyField to string of only 8000 characters
UPDATE MyTable SET MyField = @Data1 + @Data2 WHERE IDField = 1
SELECT @ptrval = TEXTPTR(MyField )
FROM MyTable
WHERE IDField = 1
-- this causes an error: Incorrect syntax near '+'.
--WRITETEXT MyTable.MyField @ptrval @Data1 + @Data2
How am I supposed to do this when local variables cannot be of type TEXT? (If I had SSQL Server 2005 I would use varchar(max) - but I don't)