Concatenation and NTEXT

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,
JonoWhy 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.
"Jono" <jono.pare@.gmail.com> wrote in message
news:1143110984.030632.152630@.g10g2000cwb.googlegroups.com...
> 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
>|||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
"Jono" <jono.pare@.gmail.com> wrote in message
news:1143110984.030632.152630@.g10g2000cwb.googlegroups.com...
> 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
>

Concatenating columns with datatype ntext

How can I concatenate 2 columns, both of type ntext, in a select statement?
I get the following error.
"Invalid operator for datatype. Operator equals add, type equals ntext"You cannot use concatenation on TEXT datatype. If the length of the value is
less than 8000, you can use the CAST function to change it to VARCHAR
datatype and do the concatenation. Otherwise, you'll have to return them as
distinct values to the client and leverage the client's string concatenation
capabilities.
Anith|||Thanks, Anith, for your reply.
Actually I was trying some data transfer between 2 databases, so the client
option is not feasible for me and length of some column values is more than
a
varchar would permit, so that's not an option either. But knowing it is not
possible is good enough, so I can think of something else.
"Anith Sen" wrote:

> You cannot use concatenation on TEXT datatype. If the length of the value
is
> less than 8000, you can use the CAST function to change it to VARCHAR
> datatype and do the concatenation. Otherwise, you'll have to return them a
s
> distinct values to the client and leverage the client's string concatenati
on
> capabilities.
> --
> Anith
>
>|||You might also want to look at the UPDATETEXT function, but you'll have to
massage each row at a time. The general workaround is to get multiple
VARCHAR columns back like:
SELECT SUBSTRING( textcol, ( 0 * 8000 ) + 1, 8000 ),
SUBSTRING( textcol, ( 1 * 8000 ) + 1, 8000 ),
SUBSTRING( textcol, ( 2 * 8000 ) + 1, 8000 ),
...
Also in SQL 2005 you will have character types with much better
capabilities.
Anith