Hi everyone,
I have a problem trying to update an NTEXT column by enclosing it
between two strings, as shown in the SQL below:

UPDATE MyTable SET NTextField = N'<pre>' + NTextField + N'</pre>'

The error I get back is this:

Invalid operator for data type. Operator equals add, type equals ntext.

Please help! I'm using SQL Server 2000.
Thanks,
Jono

Why would you want to update every column in the table with the exact same
enclosure?  You can't concatenate to an NTEXT column, and I don't see any
value in doing it the exact same way for every row anyway.

Anyway, since we are talking about HTML, this is something you have the
presentation layer do.  For example, it is easier in ASP to say:

<pre><%=rs("NTextField")%></pre>

....than to do what you are proposing.  If you really want to do this, see
the UPDATETEXT function in Books Online, but I still recommend against the
approach.




Show quoteHide quote

u need to use UPDATETEXT for this . Use the following example
-- CREATE TABLE TextExample (i int identity(1,1), text1 text, text2 text,
text3 text)
-- INSERT INTO TextExample SELECT REPLICATE('a',7998), REPLICATE('b',7998),
NULL



DECLARE @txtPtr1 Varbinary(16)
DECLARE @txtPtr2 Varbinary(16)
DECLARE @txtPtr3 Varbinary(16)

SELECT @txtPtr1 = TEXTPTR(text1)
FROM TextExample

SELECT @txtPtr2 = TEXTPTR(text2)
FROM TextExample

UPDATE TextExample
SET Text3 = Text1
WHERE i = 1


SELECT @txtPtr3 = TEXTPTR(text3)
FROM TextExample
WHERE i =1

SELECT DATALENGTH(text3)
FROM TextExample
WHERE i =1

UPDATETEXT TextExample.Text3 @txtPtr3 NULL 0 ' '

SELECT DATALENGTH(text3)
FROM TextExample
WHERE i =1

UPDATETEXT TextExample.Text3 @txtPtr3 NULL 0 TextExample.Text2 @txtPtr2

SELECT DATALENGTH(text3)
FROM TextExample
WHERE i =1







Show quoteHide quote

How can I check if it is currently executing?
Left Joins (*=)
Activity Monitor
Problem with SET QUOTED_IDENTIFIER ON
Theory on composit key